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”) |
