9.4 Your Formulas—Using a Spreadsheet
Although there may be many occasions when you are given a formula, sometimes you may need to devise your own, for example if you use a spreadsheet on a computer at home or at work. This section looks in more detail at the process of devising a formula.
Part of a spreadsheet that has been constructed to record monthly income and expenditures is shown below. It is similar to a balance sheet that you might draw up by hand and includes the monthly income and expenses, the totals, and the overall balance. It is used to keep track of expenditures, particularly to try and ensure that the balance remains positive. However, the spreadsheet has been stored on a computer rather than on paper and is updated regularly. One of the advantages of using a computer spreadsheet is that you can insert formulas into the spreadsheet to carry out calculations automatically, without using a calculator.
A spreadsheet is made up of rows and columns of cells. The columns are identified by letters and the rows by numbers. This enables you to identify each cell in the spreadsheet. For example, the number 221.12 is in column B, row 11, so this cell is B11.
Cells can be found from their reference by looking down the column and across the row. So, cell A3 can be found by looking down column A and across row 3. This cell contains the word “Salary.” Notice that cells can contain either text or numbers.
Activity: Spreadsheet Cells
Use the spreadsheet above to answer the following questions.
(a) What is contained in the following cells?
(i) The cell that is in both column A and row 5 contains the words “Total Income.”
(ii) Down column A and across row 12, cell A12 contains the word “Other.”
(iii) The cell that is in column B, row 15 contains the number 166.72.
(iv) The cell that is in column B, row 1 contains the heading “Amount ($).” This indicates that the entries in this column are amounts of money and that they are measured in $. This heading could also have been written as “Amount in $.”
(b) What is the reference for the cells that contain the following?
(i) The number 1585.18
(ii) The word "Food"
(i) 1585.18 is in column B and row 13, so its reference is B13.
(ii) “Food” is in column A and row 9, so its reference is A9.
Now, look again at the spreadsheet and see if you can work out what information it shows. For example, if you look at row 3, this shows that the monthly salary is $1,700.56. Although cell B3 only contains the number 1700.56, you know that this is measured in dollars from the heading in cell B1. Overall, the spreadsheet shows the items that make up the monthly income and where money has been spent over the month. If you were keeping these records by hand, you would then need to calculate the total income, the total expenses, and the balance.
For example, to find the total income for the month, you would need to add the salary of $1,700.56 to the other income of $51.34. This gives the total income of $1,751.90. In other words, to calculate the value in cell B5, you need to add the values in cells B3 and B4. This can be written as the following formula: