Spreadsheet Programs
Spreadsheet programs, like Microsoft Excel, have become the fundamental tool for doing business calculations, replacing calculators.
What can I use them for?
(1) Budgets
(2) Grade calculations
(3) Scheduling meetings
(4) Selecting between different business options.
They are designed to mirror the formats of budgets, and other tables of information.
What-If Calculations
In selecting the publisher of the book I am writing, I was presented with several different options.
- Some offered royalties on net price, others on gross price.
- Some suggested hardcover, some suggested softcover.
- All offered different royalties depending on how many copies were sold, say 10% on up to 3,000 copies, 12.5% from 3,000 to 5,000, etc.
- Some offered bonuses for reaching certain sales figures.
To select the best option, I needed to figure out how much I would make with each as a function of how many copies I might sell.
These kind of what if calculations apply to any business decision, including picking the right mortgage or credit card. Spreadsheets make them easy to play with.
Cells
Spreadsheets organize data into cells. Each cell has an name which is given by its row and column ID.
Each cell has three aspects:
name - what is the address of the cell?
value - what is the current numerical or textual value of the cell?
formula - is the value of the cell given or computed, and if so how is it computed?
Whenever you change the value of any cell, the formulas in the other cells recalculate new values based on the changes.
Formulas get into cells by explicitly typing them, or by copying them from one cell to the other. By "relative copying" operations, the variable names can be changed in the appropriate way.
History
The first spreadsheet program, Visicalc, was developed in 1979, running on Apple II computers. It was the first program to convince the business world that they needed personal computers.
It was such a good and simple that they soon faced still competition.
In 1983, Lotus 1-2-3 was introduced, integrating the spreadsheet, graphics, and database.
Such large software suites favor big software companies, and now Microsoft Excel is the dominant spreadsheet on the market.
Recalculation Problems
What happens if cell A1 has the formula 1+A2, and cell A2 has the formula 1+A1?
There is a recalculation cycle here which can never be satisfied. We are trapped in an infinite loop.
What might a spreadsheet program do? It might notice the problem and report it, or it might keep running forever, or it might make a fixed number of passes (say 20) over it before giving up.
This shows that we must be careful of how we organize and reference the cells. Typically, the recomputations proceed in a row-to-row or column-to-column order. In a complicated spreadsheet, it is important to know how the recomputation proceeds to make sure it is correct.
Recomputation errors are just one of many possible errors or bugs you can have in your spreadsheets. Another problem is putting the wrong formula in a given cell, putting the wrong data in a given cell, or referencing the wrong address in a formula.
Carefully checking the correctness of your spreadsheet is essential if you are going to trust your calculations! Study the formula displays!
Spreadsheet Construction as Programming
Designing and building spreadsheets can capture all the fundamental ideas of computer programming.
The three basic concepts are:
Variables - cell IDs are really just names for specific memory locations, i.e. variables.
Iteration - doing a computation over and over until it is completed. This is captured by recalculation.
Conditional tests - by placing an IF formula in a cell, we can make decisions and do logical operations. =IF( (A1 > 100000), RICH, POOR)
Conceptually, anything which can be programmed can do done using a spreadsheet. It is this power which makes spreadsheets capable of so many non-financial applications.