Once there are values in a spreadsheet, those values can be manipulated. The typical manipulation starts with an equal sign, and then the details of the calculation – most simply addition, subtraction, multiplication (using the star) and division (using the slash). These simple calculations can then be extended using these features.
Category Archives: Spreadsheet Calculations
There are many details about how Excel does calculations with functions, macros and add-ins.
Excel built-in functions
Excel has a library with many, many functions. A function consists of the name, an open parenthesis, the parameters required and then close parenthesis. For example, =AVERAGE(1,4,10) would calculate 5, the average of the three numbers.
Create a histogram with the frequency function
Instead of using the Analysis Toolpack histogram command, a histogram can also be created using the =FREQUENCY() function.
Macros
You have the ability to write your own functions or to automate tasks using the macro language built into Excel. Macros are written in VBA (visual basic for applications), but there are compatibility issues between the Windows and OS/X versions of Excel.
Add-ins for Excel
Even more complex features can be added to Excel with the use of add-ins. Some are provided with Excel but not installed, so if you have a new copy of Excel, you will have to learn a little about add-ins. There are also commercial extentions, and one that is used at the Goizueta Business School is the Decision Tools Suite from Palisades Software. This add-in provides a number of very useful tools (optimization, simulation), but is not offered in a version compatible with Mac OS/X.
What-if calculations
There are methods for calculating and saving multiple versions of your model assumptions. One tool is scenarios, but more useful for the business school are two commands: goal seek and data tables.