1 / 14

Absolute Cell References

Absolute Cell References. Unit 2 ICT GNVQ. Lesson Objectives. To use an absolute cell reference in a formula To use the www.ictgnvq.org.uk website effectively. What you are asked to do….

sezja
Download Presentation

Absolute Cell References

An Image/Link below is provided (as is) to download presentation 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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Absolute Cell References Unit 2 ICT GNVQ

  2. Lesson Objectives • To use an absolute cell reference in a formula • To use the www.ictgnvq.org.uk website effectively

  3. What you are asked to do…. • 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.

  4. The task says …… 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)

  5. Step by step….. • 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

  6. Next • 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:

  7. Now we need another 2 columns • Put the headings “NEW PRICE” and then “NEW TOTAL” in the next 2 columns

  8. Now we need to use a formula • 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

  9. Entering the formula • This is what the spreadsheet model should look like Formula displayed in formula bar New price displayed in cell F5

  10. Saving time • 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.

  11. We must use an absolute cell reference! • 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

  12. In our example • 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

  13. Calculating the new total • 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

More Related