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.
