Spreadsheet Formulae
Open a blank Microsoft Excel worksheet. Organize your screen so you can see the Excel sheet next to this web page (stop reading while you do this).
You are going to multiply some numbers.
Copy this table into your Excel sheet. Input twelve formula into column C using the steps below. For example in cell C4 the formulae would be =A4*B4 Remember to type in the formula bar.
Open a blank Microsoft Excel worksheet. Organize your screen so you can see the Excel sheet next to this web page (stop reading while you do this).
You are going to multiply some numbers.
Copy this table into your Excel sheet. Input twelve formula into column C using the steps below. For example in cell C4 the formulae would be =A4*B4 Remember to type in the formula bar.
- Type =
- Type the first cell reference
- Type the multiplication sign *
- Type the second cell reference
- Press return/enter
- Check the answer is sensible
Adding Formule to Spreadsheets
Sweet Shop Spreadsheet
Activity 5
Download Sweet Shop Spreadsheet and save it in your class folder.
Activity 5
Download Sweet Shop Spreadsheet and save it in your class folder.
5._sweets_problem_s3.xlsx | |
File Size: | 10 kb |
File Type: | xlsx |
- Paul wants to buy exactly 14 sweets with $10.
- He also wants at least one of each sweet.
- Use trial and error to find different ways to buy the sweets.
- You will need to enter an addition formulae into the two blue cells to work out the totals for the quantity and price columns (a formula can contain multiple cell references).
- The timer will be set for about 10 minutes use trial and error to solve the problem by entering numbers into the yellow cells.
- Did you spot any trends in the numbers (e.g. Can you buy more packets of jelly babies than cola bottles with $10?).
Race Points Spreadsheet
Activity 6
Download Sweet Shop Spreadsheet and save it in your class folder.
Activity 6
Download Sweet Shop Spreadsheet and save it in your class folder.
6._race_points.xlsx | |
File Size: | 15 kb |
File Type: | xlsx |
It becomes tiresome to type each cell reference and add a mathematical sign so a different formula was constructed - like a shortcut.
=SUM()
In between the brackets, you type a cell range beginning with the first cell you need to calculate and ending with the last. they need to be
consecutive like all the cells from A5 to A10. The word to is written as : . So the range would be written as A5:A10
If we were adding all the values in the cells A5 to A10 the formulae would look like this =SUM(A5:A10)
In the worksheet, Race Points, you need to work out how many points each child earned. The values in the cells have been kept deliberately low so it is easy for you to check that you used the formula correctly.
=SUM()
In between the brackets, you type a cell range beginning with the first cell you need to calculate and ending with the last. they need to be
consecutive like all the cells from A5 to A10. The word to is written as : . So the range would be written as A5:A10
If we were adding all the values in the cells A5 to A10 the formulae would look like this =SUM(A5:A10)
In the worksheet, Race Points, you need to work out how many points each child earned. The values in the cells have been kept deliberately low so it is easy for you to check that you used the formula correctly.
Shopping Bills Spreadsheet
Activity 7
Download Shopping Bills Spreadsheet and save it in your class folder.
Activity 7
Download Shopping Bills Spreadsheet and save it in your class folder.
7._shopping_bills.xlsx | |
File Size: | 14 kb |
File Type: | xlsx |
To reinforce your understanding of formulae and money:
- Select 12 items of food/drink which you would buy for a week’s worth of lunch box meals.
- Work out how much change you would get from $10.
- Copy each desired item’s name and price over from the prices sheet onto the receipt sheet.
- In the first blue cell, enter the appropriate formulae needed to calculate the total cost, =SUM()
- In the second blue cell, enter a simple subtraction formula to work out the amount of change that would be received from $10 (you might have to change your earlier purchases if the result is a negative number [i.e. they had overspent]).
Extension Task
- Read the list of 7 ingredients to make a birthday cake and enter them in the spreadsheet.
- Using the price list, enter the cost.
- Work out how much change you would get from $10.
- In the first blue cell, enter the appropriate formulae needed to calculate the total cost, =SUM()
- In the second blue cell, enter a simple subtraction formula to work out the amount of change that would be received from $10.
9._pocket_money_problem.xls | |
File Size: | 30 kb |
File Type: | xls |
- You need to work out which method of getting pocket money over a ten week period you prefer. (i.e. You need to identify which one would give you the most money in total after ten weeks).
- Since the four possible options are very similar, a structured table in a spreadsheet would be the most convenient way to find a solution.
- Decide which formulae will help you.
- Don't forget to give a reason why.