The Formula and Function Page

Sac State Home \| College of Business *The Function and Formula Page*
*A Little History* Back in the early days of the personal computer, there wasn't too much that these machines could do. That changed with the first "killer app," a spreadsheet program intended to harness the computer's power and work with numbers called Lotus 123. Suddenly, calculations that were tedious, difficult, even impossible to do became effortless - almost. Many of these operations and processes within Microsoft Excel were pioneered by Lotus. These include the use of FUNCTIONS and FORMULAS and how to build and utilize them. Your Portfolio Theory class uses Microsoft Excel, and introduces many of the program's formulas and functions. These are a couple of the tools that create the nearly effortless "number-crunching". "Almost" effortless, because if you do not know what is available within the program, and how to use them, there results much annoyance, and difficulty with Excel. Of note: NO one knows every tool! Throughout the course, you will become a proficient user of functions and formulas. Additionally, you may have many questions. Many of the answers will be found below. This information is in a finance context only. Also check the FAQ and "Quik fx" Guide for other possible answers. But, please be advised, this website is a "work in progress." As a result, if you find a problem, error, or solution ...please email the webmaster. *Formulas* Formulas are equations that you build that perform operations on the information within your worksheet. Formulas can perform mathematical operations, such as addition and multiplication, or they can compare worksheet values or join text. They can use information you provide or refer to other cells (on the same worksheet, on other sheets in the same workbook, or even on sheets in other workbooks). Formulas can be as simple as adding two numbers ("=5+3") or as complex as you make them, including many formulas, nested formulas, and functions (you will be building some complex ones!). You must remember the Order of operations in math (Ex) Multiplication before addition. Accordingly, all formulas calculate values from left to right and always begins with an equal sign (=) ... although a vestige of the Lotus 123 past allows functions to start with the plus sign (+). Starting with an equal sign tells Excel, "Formula coming!" The succeeding characters constitute the formula and the elements to be calculated - basically the "what I want this thing to do". Functions Functions are simply pre-designed formulas. They are at the heart of Excel and your class project. Some are simple timesaving tools, and some do very powerful things. For example, if you want to add a column of 50 numbers starting in cell A1, you might enter in cell B1: =A1+A2+A3+...+A50. Or you could use a function called Sum. In that case it would be: =sum(A1:A50). Much easier!