Why the dollar $ is needed?

[BNCOXUK]

Hi, I have a question regarding the dollar $. Below there are two formulae to calculate the standard deviation. For the first one, there is no dollar sign for C10:C45, but there is for the second one $A$10:$A$389.

={STDEV(IF(ISNUMBER(C10:C45),C10:C45-E10:E45,""))*SQRT(12)}

={STDEV(IF($A$10:$A$389>=B5,C10:C389,""))*SQRT(12)}

If I remove the dollar $ for the second one, then the error #ERROR occurs.


Thanks a lot!

[Ron Coderre]

I think the real issue is that the formula is an ARRAY FORMULA and must be completed by pressing CTRL+SHIFT+ENTER (instead of just ENTER).

The dollar sign ($) is just a flag to Excel to "lock" that
part of the reference when copy/pasting the cell. The
part of the reference preceded by the $ are called
an ABSOLUTE REFERENCE. The part not preceded by
a $ is called a RELATIVE REFERENCE.

Examples:
A1: =B1
Copy A1 and paste in C2.
The formula in C2 is: =D2

A1: =$B1
Copy A1 and paste in C2.
The formula in C2 is: =$B2

A1: =B$1
Copy A1 and paste in C2.
The formula in C2 is: =D$1

A1: =$B$1
Copy A1 and paste in C2.
The formula in C2 is: =$B$1

For more information, search Excel Help for "range references"
and see the "about cell and range references" section
and select the "The difference between relative and absolute references"
topic.

[BNCOXUK]

Hi Ron,

Thanks a lot for your help. Got it now!