OpenOffice Calc Formulas

If you don't have access to Microsoft Excel, you can do most of the calculations from the book using either OpenOffice.org Calc Spreadsheet Program  or LibreOffice Calc Program. Both programs are open-source free products. These software suites have come a long way in recent years and though not yet quite as functional as Microsoft Office, they are pretty damn close.

I tested most of the examples from the book in OpenOffice.org Version 3.0, and have put together some notes on what works, what doesn't, and what works a little differently. 

Calc Notes:

  • All the basic formulas work pretty much the same as in Excel, however, functions that require arguments have a slightly different syntax. Basically, wherever you would use a comma in an Excel function, you would instead use a semicolon in Calc. See example below.
 Excel Formula
=IF(ISBLANK(M7),J7*K7*L7,M7)
 Calc Formula   =IF(ISBLANK(M7);J7*K7*L7;M7)
  • I could not find a Regression Tool in Calc similar to Excel's, so you won't be able to do the regression example. However it does have the Forecast function which works the same as Excel (using semicolons instead of commas).
  • OpenOffice.org Version 3.0 Calc does have a Solver add-in, but it is more limited than the one in Excel and I could not get it to work with the examples in this book.
  • WEEKNUM function works a little different than in Excel. You need to enter the “Mode” that sets the start day of the week. So instead of the Excel formula =WEEKDAY(A3) you would enter =WEEKDAY(A3;1) . Also, Calc may return different week numbers than Excel. 

 

Cut&Paste Formulas for Calc.

Here is a "cheater page" for some of the longer and more complicated formulas. These formulas have been adapted to the Calc Spreadsheet Program.  This is NOT all of the formulas from the book. It is only some of the longer ones or ones with special characters where a simple typo could cause you some serious aggravation.

The table below references the page number and figure number from the book, and the spreadsheet Cell Reference where the formula would be entered. Just triple-click on the formula and it should highlight. Then just copy it (CTRL+C), and paste it (CTRL+V) into your spreadsheet.

 

Page Figure Cell

Calc Formula  (NOT FOR EXCEL)

81 4C C4 =IF(ISBLANK(B3);B4*C5*C6*C7*C8*C9;B3*C5*C6*C7*C8*C9)
110 5G G7 =IF(ISBLANK(B6);G6;(F7*G$2)+(G6*(1-G$2)))
110 5G I7 =IF(ISBLANK(B6);G7+I6;E7+((1/E$2)*G7))
114 5H I7 =IF(ISBLANK(B6);(IF(ISBLANK(H7);I6+G6;(H7*I6)+I6));E7+(1/E$2)*(IF(ISBLANK(H7);G7;H7*E7))
114 5H N7 =IF(ISBLANK(M7);J7*K7*L7;M7)
132 6A B4 =(B3-AVERAGE($B3:$H3))^2
132 6A H5 =SQRT(SUM(B4:H4)/(COUNT(B4:H4)-1)
137 6C J6 =SQRT(SUM(B5:J5)/(COUNT(B5:J5)-1))
144 6F C10 =IF(B10>0.5;NORMSINV(B10);0)
173 7D B7 =SQRT((2*B2*B5)/(B3*B4))
173 7D B10 =(B3*0.5*B9*B4)+(B2*B5/B9)
176 7G D9 =IF(ISBLANK(C9);"";IF(E9>B9;(C$5*0.5*E9*C9)+(C$4*C$6/E9)+(C$6*C9);(C$5*0.5*B9*C9)+(C$4*C$6/B9)+(C$6*C9)))
176 7G E9 =IF(ISBLANK(C9);"";(SQRT(2*C$6*C$4/(C$5*C9))))
176 7G F9 =IF(D9=D$16;(B9);"")
176 7G G9 =IF(ISBLANK(F9);"";IF(E9>F9;(E9);F9))
181 7K B15 =SQRT((2*B2*(SUM(B8:M8))/(B3*B4)))
181 7K B16 =COUNTIF(B10:M10;”>0”)