How to use the $ to do global func> =ave($1:$1) more details?

A

AlanY

I have seen it used but want more details and suggested site(s) where
examples are listed. The one I have seen calculates the average across say a
row, but I'd like more information on alternative uses.

Thank you.
[email protected]
 
K

kassie

One of us must be totally confused here! The $ sign has nothing to do with
the AVE or average function. 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)
 
G

Gord Dibben

Kassie

Very good explanation and examples of the usage of the $ sign in cell
references.

Hope you don't mind if I keep it handy for future postings.


Gord Dibben MS Excel MVP
 
A

AlanY

I was not referring to what you mentioned which is clear. I am referring to a
cell address of $6, $7, etc, without the column letters showing . That is
the key to making the function dynamic.
 
A

AlanY

I was not referring to absolute references but the cell address without the
column letter, such as $5, $6, $7, etc. That is totally different from the
aforementioned which deals with absolute vs relative addresses.
 
B

Biff

Not sure what you're asking for here.....

$1:$1 refers to ALL of row 1, A1:IV1

$A:$A would refer to ALL of column A, A1:A65536

Biff
 
P

Peo Sjoblom

Are you talking about R1C1 reference where columns are labeled numerically

=R[-2]C[1]

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
G

Gord Dibben

=AVERAGE($6:$6) means average the cells in row 6

=AVERAGE($6:$7) same for rows 6 and 7

The $ sign still makes it Absolute Reference


Gord
 
A

AlanY

NO
Peo Sjoblom said:
Are you talking about R1C1 reference where columns are labeled numerically

=R[-2]C[1]

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


AlanY said:
I was not referring to absolute references but the cell address without
the
column letter, such as $5, $6, $7, etc. That is totally different from
the
aforementioned which deals with absolute vs relative addresses.
Click to expand...
Click to expand...
 
A

AlanY

yes Correct Biff; however, when I try to do an average of data in a column
that will be CHANGING, and I want the average to be dynamic, it does not seem
to work:

=AVERAGE(A$:A$) to average column A as data is entered. versus say
=AVERAGE(A1:A25). I am utilizing this structure in conjunction with advanced
SPC Controlcharts that look at changes in both Mean and St Deviation, ie
shifts from control points. The dynamic average approach works better. Unless
I get more detail, I will have to utilize a Transpose Link.

Thank you and keep responding.
 
P

Peo Sjoblom

But there are no dynamics involved, all you do is using the whole
column/row, if you want a dynamic range that will average values based on
how many values are entered so it will expand if you add more values and
decrease if you remove values you can use a define named range and give it a
name

=OFFSET(Sheet2!$A$1,,,,COUNT(Sheet2!$1:$1))

give it a name like MyRange

and use

=AVERAGE(MyRange)

the above would be a dynamic range for row number 1 in Sheet2 and you would
use insert>name>define, put the formula in the refers to box and
out your name in the name box

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey
 
Top