Using the "$" as part of a cell reference

A

awy32

Could someone please explain in more detail the usage of above. I have
seen this usage in:

=AVERAGE($6:$6) to yield a dynamic average across row 6. This does not
seem to work in a Column setup. I am looking for more details, links,
etc.

Thank You.
Alan
 
M

Marcelo

Hi the use of $ is to change a formula absolute to relatvie, you could use it
in a Column as =AVERAGE($A:$A) or in specific range or unique cell

hth
regards from Brazil
Marcelo

"awy32" escreveu:
 
G

Gord Dibben

Recent posting by Kassie...........

The $ sign is used to make a cell address an
absolute one.

If you copy any formula that refers to a cell address, say =SUM(A1:A10) to
the next row, Excel changes the formula to read =SUM(A2:A11).
If however, you copy a formula reading =SUM($A1:A10) , Excel changes the
formula to
Copy down =SUM($A2:A11).
Copy right =SUM($A1:B10)
If you enter the formula as =SUM($A$1:A10), it will change to
Copy down =SUM($A$1:A11),
Copy right =SUM($A$1:B10)
The formula =SUM(A$1:A10, will change to
Copy down=SUM(A$1:A11),
Copy right =SUM(B$1:B10)
If you enter it as =SUM($A$1:$A$10), and you copy it to any other location
on your spreadsheet, it will still read =SUM($A$1:$A$10)
In other words, the $ sign in front of a column reference loks subsequent
copies to that column. The $ in front of a row reference, locks subsequent
copies to that row. You can also insert the $ before the latter parts of the
formula, to lock either the column or row reference, eg =SUM(A1:$A10), which
can change as follows: Copy down =SUM(A2:$A11)
Copy right =SUM(B1:$A10)

=SUM(A1:A$10) will change to
Copy Down =SUM(A2:A$10)
Copy right =SUM(B1:B$10)


Gord Dibben MS Excel MVP
 
L

L. Howard Kittle

Along with Gord's explanation you might want to see what the F4 key will do.
Type =A1, then before hitting Enter, hit the F4 key four times and notice
the reference changes.

HTH
Regards,
Howard
 
Top