$ sign

[Antonia]

Hi,

I wondered if someone could explain what the $ sign means in a formula.
I've inherited a spreadsheet with lots of them appearing all over the place
but I can't work out how to break down the formule; here's an example:

=IF(N29=$F$3,Q29,0)

Many thanks,

 

[John C]

Go into your xl help, and search on the absolute and relative references.

 

[Mike H]

Hi,

It fixes that reference as 'absolute'. that means if you drag the formula a
reference with $ in front won't change.

The options are

A1 - column and row relative
$A1 - column absolute - row relative
A$1 - Column relative - row absolute
$A$1 - column and row absolute

Try dragging your formula down and note that $F$3 wont change.

Mike

 

[BobT]

This sets the row or column reference to Absolute. In a nutshell, when you
copy that formula, let's say down a row, the original cell will have:

=IF(N29=$F$3,Q29,0)

The new cell will have the formula:

=IF(N30=$F$3,Q30,0)

Note the difference? The non-$'d cell references have thier rows increased
as you copy down (decreased if you copy up). If you copied it to the right
(up a letter) then the N becomes O and Q becomes R but the $F stays as $F.

 

[Jon]

It locks the cells in the formula. For example, if you copy/drag that
function down one cell, it will lock to cell F3 due to the $ signs, while Q29
will change to Q30.

jon

 

[Antonia]

Hi John, Bob, Mike and Jon,

Thank you all for explaining that to me - it makes perfect sense now!

Antonia