Loading in 5 sec....

Absolute Cell ReferencesPowerPoint Presentation

Absolute Cell References

- 164 Views
- Uploaded on
- Presentation posted in: General

Absolute Cell References

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Absolute Cell References

Unit 2 ICT GNVQ

- To use an absolute cell reference in a formula
- To use the www.ictgnvq.org.uk website effectively

- During a one week “SALE” in March the supermarket discounts everything by 15%. The Wright family want to use the spreadsheet model to help them predict how much extra money they will have available to spend on other things.

Enter the discount as 15% in a cell near or at the top of the spreadsheet. Create two new columns (with suitable headings)

- to show the new price charged for each of the items when they are discounted (use an absolute cell reference in your formulae in this new column so that you can easily change the rate of discount).
- to work out the new total amount paid (use a formula - quantity multiplied by new price)

- First insert two or three rows at the top of your spreadsheet model:

Highlight the top row of your spreadsheet model, click “Insert” then “rows”. This will insert one row. Now do this once or twice more to insert two or three rows

- Add a label in one of the cells (here we have used cell B2) – and the discount percentage in cell C2 next to the label – like this:

- Put the headings “NEW PRICE” and then “NEW TOTAL” in the next 2 columns

- We need a formula to automatically calculate the new price by taking off the discount.
- In cell F5 this should be
=B5-(B5*C2)

- This means – start with the original price and take away 15% (or whatever percentage figure is shown in cell C2) of it

- This is what the spreadsheet model should look like

Formula displayed in formula bar

New price displayed in cell F5

- To save time we would normally replicate the formula down the column
- If we do then our formula will change – see the example below:

The formula used in cell F6 will be this:

This uses cell C3 in the formula – it is empty – so the amount shown in cell F6 is the same price as before. We need to find some way to make sure the formula always uses the cell address C2 in each formula.

- To make a cell address stay the same in a formula when it is copied or replicated just add the dollar signs $$$$$$$$$…
- So $C$2 is exactly the same as using C2 in a formula – except that when you copy or replicate it to somewhere else it will NOT CHANGE

- If you include the $ sign before each part of the cell reference C2 – making it $c$2 – the cell reference will stay the same when you replicate the formula

- You can now enter a formula in cell G5 to calculate the new total for “value sliced bread” – replicate this down the column – and use the SUM function to add up the full shopping list
- Finally you can use a formula to calculate the savings when there is 15% discount for all items – see final slide