*Tough Math Question*

[Spreadsheet]

I have a column C of numbers. I need to find the median value of thes
numbers. Then I need to know the average distance between each numbe
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 eac
number in C and the median of C. Then I can use AVERAGE(D1100) to ge
the average distance from the median.

However, I don't want this column D. By itself D is meaningless an
takes up valuable space. I am only interested in the averag
difference, not each individual difference. Is there a way to arrive a
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 i
capable of this. Any help would be appreciated. Thanks

 

[Guest]

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

entered with Control+Shift+Enter

 

[Lewis Clark]

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.

--


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(D1100) 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]

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

 

[Domenic]

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!

 

[Guest]

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(INDIRECT("311!N2:N"&A1)-MEDIAN(INDIRECT("311!N2:N"&A1)))))

where cell A1 contains the ending row number.

 

[Guest]

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]

THANKS JMB, your suggestion worked well