I wonder what the $ sign means ?

Eg. =LOOKUP(B5,Sheet1!$H$6:$H$10,Sheet1!$I$6:$I$10)
=IF(B4=0,Sheet1!$B5,Sheet2!$B4+1)

And why in this case =IF(B5=0,Sheet1!B6,Sheet2!B5+1) has no $ sign ?

Pls answer my query thanks.

Dear pl;

Eg. =LOOKUP(B5,Sheet1!$H$6:$H$10,Sheet1!$I$6:$I$10)
=IF(B4=0,Sheet1!$B5,Sheet2!$B4+1)

Just an example: When the above is copied to the next cell, there will be no change in H and 10, I and 10. Usually when we copy a formula to the next cell eg: H10 to H11, the formula will change automatically to H11 without you changing the formula. Hence once the dollar sign is use, the formula will fix at H and 10.
In other words the use of dollar sign result in contant value.

And why in this case =IF(B5=0,Sheet1!B6,Sheet2!B5+1) has no $ sign ?

Here there is no dollar sign. So the values are variable which is just opposite to what I had said above.

Hope you understand. Please do not hesitate to ask should you come across further problems.

Regards
Christopher Foo

david says: good answer, Chris. but just in case someone reading this is still wondering, please allow me to offer a little additional explanation:

PL's question comes from the plan.xls spreadsheet which i showed you in class last week and you can download from here.

LOOKUP(B5,Sheet1!$H$6:$H$10,Sheet1!$I$6:$I$10)

this formula (which is in Sheet 2, cell L5 of the speadsheet) calculates the annual cost of a child whose age is in cell B5 by looking up the value in B5 in the table Sheet1!$H$6:$H$10
and choosing the value in the corresponding row of table Sheet1!$I$6:$I$10

the table contains cost estimates for children of different age ranges.

Now, to be able to calculate the cost of several children of different ages, we have to calculate the cost of each one and add them up.

Sheet 2 contains a large working table whose values are calculated from the data supplied by the spreadsheet user in sheet 1.

It's a rather clumsy way of doing the calculation, but Excel doesn't have a repeat function like Logo - personally i think it should have one, and have in the past used a product created by Xerox Corporation Paolo Alto Research Centre called "The Analyst" which is like Excel except you can write a whole program (in a famous programming language called "SMALLTALK") in a cell, not just a single formula. The Analyst was created by Xerox at the request of the CIA who wanted to use it for tracking the connections between people suspected of being involved in drug-running. Xerox turned it into a generic product for sale on the open market. I used it in Australia for a mining and steel company called BHP, to do some calculations on Key Performance Indicators of corporate performance, so the executives of the company could see what might be likely to happen in the future.

anyway, back to the spreadsheet... if you look across the cells of columns L to U of sheet 2, you will see the same formula as in cell L5, except that the value being looked up is different. Note that whilst i want to look up a different value each time, i always want to look it up in the same table (Sheet1!$H$6:$H$10).

To make those formulas, all i had to do was copy the formula in cell B5 across and down. Excel automatically put in the references i wanted.

The $ signs tell Excel not to change the row letter and column number when it copies the formula into a different cell of sheet 2.

IF(B5=0,Sheet1!B6,Sheet2!B5+1)

this formula is in cell B6 of sheet 2. It got there because i copied the formula in cell B5 into cell B6.

The formula in cell B5 is

IF(B4=0,Sheet1!B5,Sheet2!B4+1)

It calculates the age of the first child, as described in the comment on cell B5 (to see it, click the little red ticket at the top right corner of cell B5).

In this case, i want the formula in B6 to calculate values from B5 and Sheet1!B6 (rather than B4 and Sheet1!B5) That is, i want Excel to change the row and column references in the formula as it copies them down.

[note: i could have written B4 instead of Sheet2!B4 because the formula is being used in sheet 2.... Excel put the Sheet2! prefix in for me, thinking it was being helpful, and i didn't bother to take it out]

familyPlan.xls looks a bit complicated - and it took me a little while to make it - and it's a bit more complicated than the spreadsheet you will make for your project, but sheet 2 only uses 3 formulas to calculate the cost of any child in any year of marriage, based on when it is planned to be born and how much (the spreadsheet user estimates that) children of various ages cost.

i showed it to you for 2 reasons:
1. so you can see how to use the LOOKUP function
2. so you can have a useful tool for planning your own families

i'm very blur though even after the explanation.

i hope we don't have to use those formula in the exam...?

david says: i will give you a simplified explanation in class today. here is a link to an introductory online tutorial. and here is an easy to follow video showing you how to copy formulas. and here is one that explains the difference between what Excel calls absolute and relative cell references.

these videos are pretty nice. i think i will mention them in the class notes for next year.
thanks, PL, for helping me to improve my teaching