Friday, September 25, 2015

HP Prime Spreadsheet App Tutorial 7: Absolute References


HP Prime Spreadsheet App Tutorial 7:  Absolute References



Lesson 7 Objectives:

* Use Absolute References

Background:

When a formula is copied into different cells, the Spreadsheet app adjust the cells used.  For example, say that cells A1:B10 have numeric data and I want to find the sum of each column.  I type =SUM(A1:A10) in cell A11.   I don’t have to readjust the formula if I just copy the formula from cell A11 to B11.  The app automatically adjusts the formula in cell B11 to =SUM(B1:B11).  This is known as relative reference.

This is a great feature but there times where we don’t want the cell reference to change.  Say in cell C1 I have a rate that is multiplied to the sum of both columns.  In this case, we can use an absolute cell reference, so that when the formula is copied from A11 to B11, the C1 reference remains locked.  To make an absolute reference, add a dollar sign ($) to the column and row indicators.  Hence, in our example, A11 would contain the formula =$C$1*SUM(A1:A10).  When the formula is copied to B11, its formula would have =$C$1*SUM(B1:B10).

We can freeze only the column ($C1), only the row (C$1), or both ($C$1).

In today’s lesson, we are going to build a spreadsheet that contains item and the tax for each item (see picture at the top of this blog entry).

Steps:

1.     Select the cell with the hp logo (upper left hand cell).  Format the spreadsheet so that it is set to 2 decimal places.   (Format), 1 for Number Format, 3 for Fixed.  (Format), 1 for Number Format, 8 for Decimal Places, select 2.
2.    Enter the text in the following cells:
·         A1:  “Rate”
·         B2:  “Price”
·         C2:  “Tax”
·         A3:  “Discs”
·         A4:  “Paper”
·         A5:  “Staples”
·         A6:  “USB”
3.    Enter the following amounts:
·         B1 = 0.09   (9%)
·         B3 = 9.99
·         B4 = 8.99
·         B5 = 14.99
·         B6 = 12.99
4.    Go to cell C3.  Press (Select) and then arrow down to C6.  The range C3:C6 should be selected.
5.    Type =B3*$B$1.  B1 will be an absolute reference.  You can get the $ sign by pressing the soft key ( $ ) while you are entering a formula, text, or numeric data.
6.    Change the rate in cell B1 (use the decimal format).  Observer the change in cells C3:C6.





This concludes Lesson 7.  On the next installment we will work with statistical analysis.  Until next time,

Eddie


Note:  This is my 500th post on this blog.  Thank you to you all!


This blog is property of Edward Shore, 2015.



1 comment:

HP Prime and TI-84 Plus: Basic Wheatstone Full Bridge Circuit

HP Prime and TI-84 Plus:  Basic Wheatstone Full Bridge Circuit The program WHEATSTONE (HP Prime) and WHTSTONE (TI-84 Plus CE) deals wi...