Tips for Using OpenOffice Calc for ESD.70IntroductionCompatibility of Calc with ESD.70Non-Intuitive Differences in CalcFormula Variable Separator is the Semi-colonShortcut to Fixed Cell Reference is SHIFT + F4Worksheet Reference is Relative'Multiple Operations' instead of Data TablesAutomatic/Manual Calculation Toggle is in Different PlaceConclusionMIT OpenCourseWare http://ocw.mit.edu ESD.70J / 1.145J Engineering Economy Module Fall 2009 For information about citing these materials or our Terms of Use, visit: http://ocw.mit.edu/terms.Tips for Using OpenOffice Calc for ESD.70 By Nathan Fleming Sept. 12, 2008 Introduction This document lists some lessons learned while trying to use OpenOffice Calc in place of Microsoft Excel for course ESD.70 (Engineering Economy Module). It is intended to make life easier for those who try to do the same. It is not a comprehensive list of the differences between the two programs. It instead deals with a few specific issues that came up when using Calc for ESD.70. Compatibility of Calc with ESD.70 Everything taught in ESD.70 on Microsoft Excel can be performed in OpenOffice Calc—with one major exception: Calc cannot perform the Monte Carlo simulation that is taught in class and needed for the homework. Neither OpenOffice Calc 2.4 or 3.0 Beta were capable of performing the Monte Carlo simulation as of the writing of this document. The Monte Carlo simulation is performed by a combination of the RAND() function and the data table feature in Excel. Calc has the RAND() function, and is able to replicate the data table feature using a feature called 'multiple operations'; however, when Calc tries to create a data table using a formula based on RAND(), it fills the data table with identical values of output. It is supposed to produce varied results by having varied the inputs to the formula using the RAND() function—this is what is does in Excel. Two thousand identical outputs do not make a Monte Carlo simulation. When it comes time to do the homework, Calc users will have a choice to make: They can either open their spreadsheet in Excel to run the Monte Carlo, which is what I did. Or, they can try to find a workaround within Calc. I could not find a workaround that did not involve adding considerable complexity to the spreadsheet, but I am sure someone cleverer or more stubborn than I could figure one out. That said, the material taught in ESD.70 will be useful to both Excel users and Calc users. You can follow along in class using Calc with minimal problems—especially if you use the tips shown in this document. Being a relatively experienced Excel user and a novice Calc user, ESD.70 helped me learn some higher-level techniques for both programs and helped improve my literacy in Calc. Non-Intuitive Differences in Calc Most operations in Calc are identical or completely analogous to those in Excel. The following is a list of differences between the way Calc and Excel operate that were not immediately intuitive to me and my Calc-using classmates.Formula Variable Separator is the Semi-colon When writing formulas in Calc, they character that separates the input variables is not the comma (,), it is the semi-colon (;). Example: In Excel a function that is spelled out: =min(A5,A6,A7) would be written in Calc as =min(A5;A6;A7) Shortcut to Fixed Cell Reference is SHIFT + F4 In Excel, hitting the 'F4' key with your cursor on or immediately after a cell reference, will cycle through (with each press of the key) various combinations of location fixing (fixed column, fixed row, both fixed column and row). In Calc the same function is performed by holding SHIFT while hitting F4. It is also important to note that Calc offers you the option to fix the sheet reference as well (see next item.) Worksheet Reference is Relative Excel treats worksheet references as fixed. In Calc, they are relative unless you fix them with a dollar sign prefix —just like you would for row and column references. For example, if you copy and paste a formula which references a cell on different worksheet—say the worksheet immediately to the left of the worksheet the formula is in. When you paste the copied formula, the formula will reference the worksheet in the same relative position to the new sheet (immediately to the left in this example.) You can make Calc behave like Excel by putting a dollar sign ($) before the worksheet reference in the formula. The fixed cell reference shortcut will add the dollar sign for you. 'Multiple Operations' instead of Data Tables The analogous function to Excel's Data Tables in Calc is the 'Multiple Operations' Feature. Instead of seeing 'Table...' on the 'Data' drop down menu, as you would in Excel, you will see the analogous 'Multiple Operations...' feature. Multiple Operations works the same way as Excel's 'Table' feature, with one significant improvement: you specifically choose the cell containing your formula. Excel forces you to always have your 'formula' cell on top of your column for a one way data table and in the top-left corner for a 2-way. Calc lets you put it where you like (as long as it's on the same page). This apparent advantage of Calc is offset by the fact that multiple operations does not work well in concert with the RAND() function. Instead of using the RAND() function multiple times, it seems to use it only once. Automatic/Manual Calculation Toggle is in Different Place In Excel, the 'calculate' tab under 'Tools' > 'Options...' lets you toggle between manual and automatic calculation. You will not find the same feature on the Calc options menu. Instead, to toggle between manual and automatic calculation in Calc, you got to 'Tools' > 'Cell Contents...' and check or un-check 'AutoCalculate'. Conclusion By using the lessons learned laid out in this document, students who wish to use OpenOffice Calc for ESD.70 should be spared some unnecessary interruptions to the flow of their classroom