I have a column C of numbers. I need to find the median value of these
numbers. Then I need to know the average distance between each number
in the column and the median of the column.

Currently I am using this function:

D1=ABS(C1-MEDIAN(C1:C100))
D2=ABS(C2-MEDIAN(C1:C100))
...
D100=ABS(C100-MEDIAN(C1:C100))

This gives me a new column D which contains the distance between each
number in C and the median of C. Then I can use AVERAGE(D1:D100) to get
the average distance from the median.

However, I don't want this column D. By itself D is meaningless and
takes up valuable space. I am only interested in the average
difference, not each individual difference. Is there a way to arrive at
the average difference without creating this column D?

I want something like: AVERAGE(ABS(Cn-MEDIAN(C1:C100)),n=1...100)

This is what I want in mathematical terms. I don't know if Excel is
capable of this. Any help would be appreciated. Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

Maybe
=AVERAGE(ABS(C1:C100-MEDIAN(C1:C100)))

entered with Control+Shift+Enter

"Spreadsheet" wrote:


I have a column C of numbers. I need to find the median value of these
numbers. Then I need to know the average distance between each number
in the column and the median of the column.

Currently I am using this function:

D1=ABS(C1-MEDIAN(C1:C100))
D2=ABS(C2-MEDIAN(C1:C100))
...
D100=ABS(C100-MEDIAN(C1:C100))

This gives me a new column D which contains the distance between each
number in C and the median of C. Then I can use AVERAGE(D1:D100) to get
the average distance from the median.

However, I don't want this column D. By itself D is meaningless and
takes up valuable space. I am only interested in the average
difference, not each individual difference. Is there a way to arrive at
the average difference without creating this column D?

I want something like: AVERAGE(ABS(Cn-MEDIAN(C1:C100)),n=1...100)

This is what I want in mathematical terms. I don't know if Excel is
capable of this. Any help would be appreciated. Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

How about:
=AVERAGE(ABS(C1:C100-MEDIAN($C$1:$C$100)))

This is an array formula, and must be committed with Control-Shift-Enter, not just the enter key. If you do it correctly, Excel will put curly brackets {} around the formula - these can't be typed manually.

--

"Spreadsheet" wrote in message ...

I have a column C of numbers. I need to find the median value of these
numbers. Then I need to know the average distance between each number
in the column and the median of the column.

Currently I am using this function:

D1=ABS(C1-MEDIAN(C1:C100))
D2=ABS(C2-MEDIAN(C1:C100))
...
D100=ABS(C100-MEDIAN(C1:C100))

This gives me a new column D which contains the distance between each
number in C and the median of C. Then I can use AVERAGE(D1:D100) to get
the average distance from the median.

However, I don't want this column D. By itself D is meaningless and
takes up valuable space. I am only interested in the average
difference, not each individual difference. Is there a way to arrive at
the average difference without creating this column D?

I want something like: AVERAGE(ABS(Cn-MEDIAN(C1:C100)),n=1...100)

This is what I want in mathematical terms. I don't know if Excel is
capable of this. Any help would be appreciated. Thanks.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

Thanks to those who replied. USing the array formula did give me some
success. Here is the equation that I am using. It draws data from a
sheet called 311.

{=AVERAGE(ABS('311'!N2:'311'!N45-MEDIAN('311'!N2:'311'!N45)))}

However, this formula only works when I manually enter in the 45 (only
N2:N45 contains data). In other words, if I had '311'!N2:'311'!N100
instead of '311'!N2:'311'!N45, the formula wouldn't work. But then if I
add some new data to the end of the list, I would want
'311'!N2:'311'!N46 instead of '311'!N2:'311'!N45. How can I make this
number automatically change. I know that INDIRECT will do the trick
(since I have another cell that counts the number of rows of data in
the list), but I'm not sure about the syntax of INDIRECT when
referencing cells in other sheets. If anyone can help, please reply


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

Try...

=AVERAGE(IF('311'!N2:N45<"",ABS('311'!N2:N45-MEDIAN('311'!N2:N45))))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
Spreadsheet
wrote:

Thanks to those who replied. USing the array formula did give me some
success. Here is the equation that I am using. It draws data from a
sheet called 311.

{=AVERAGE(ABS('311'!N2:'311'!N45-MEDIAN('311'!N2:'311'!N45)))}

However, this formula only works when I manually enter in the 45 (only
N2:N45 contains data). In other words, if I had '311'!N2:'311'!N100
instead of '311'!N2:'311'!N45, the formula wouldn't work. But then if I
add some new data to the end of the list, I would want
'311'!N2:'311'!N46 instead of '311'!N2:'311'!N45. How can I make this
number automatically change. I know that INDIRECT will do the trick
(since I have another cell that counts the number of rows of data in
the list), but I'm not sure about the syntax of INDIRECT when
referencing cells in other sheets. If anyone can help, please reply

Try
=AVERAGE(ABS(INDIRECT("311!N2:N"&A1)-MEDIAN(INDIRECT("311!N2:N"&A1))))

or, if using Domenic's formula (which excludes empty cells)
=AVERAGE(IF(INDIRECT("311!N2:N"&A1)<"",ABS(INDIRE CT("311!N2:N"&A1)-MEDIAN(INDIRECT("311!N2:N"&A1)))))

where cell A1 contains the ending row number.


"Spreadsheet" wrote:


Thanks to those who replied. USing the array formula did give me some
success. Here is the equation that I am using. It draws data from a
sheet called 311.

{=AVERAGE(ABS('311'!N2:'311'!N45-MEDIAN('311'!N2:'311'!N45)))}

However, this formula only works when I manually enter in the 45 (only
N2:N45 contains data). In other words, if I had '311'!N2:'311'!N100
instead of '311'!N2:'311'!N45, the formula wouldn't work. But then if I
add some new data to the end of the list, I would want
'311'!N2:'311'!N46 instead of '311'!N2:'311'!N45. How can I make this
number automatically change. I know that INDIRECT will do the trick
(since I have another cell that counts the number of rows of data in
the list), but I'm not sure about the syntax of INDIRECT when
referencing cells in other sheets. If anyone can help, please reply


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

Forgot one other thing. You could also use a dynamic named range. Too many
of these, however, and your spreadsheet will probably slow down as OFFSET is
a volatile function.

http://www.cpearson.com/excel/excelF.htm#DynamicRanges


"Spreadsheet" wrote:


Thanks to those who replied. USing the array formula did give me some
success. Here is the equation that I am using. It draws data from a
sheet called 311.

{=AVERAGE(ABS('311'!N2:'311'!N45-MEDIAN('311'!N2:'311'!N45)))}

However, this formula only works when I manually enter in the 45 (only
N2:N45 contains data). In other words, if I had '311'!N2:'311'!N100
instead of '311'!N2:'311'!N45, the formula wouldn't work. But then if I
add some new data to the end of the list, I would want
'311'!N2:'311'!N46 instead of '311'!N2:'311'!N45. How can I make this
number automatically change. I know that INDIRECT will do the trick
(since I have another cell that counts the number of rows of data in
the list), but I'm not sure about the syntax of INDIRECT when
referencing cells in other sheets. If anyone can help, please reply


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659

THANKS JMB, your suggestion worked well.


--
Spreadsheet
------------------------------------------------------------------------
Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730
View this thread: http://www.excelforum.com/showthread...hreadid=547659