*Tough Math Question*

[Spreadsheet]

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.

[JMB]

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
>
>

[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.

--

"Spreadsheet" <[email protected]> wrote in message news:[email protected]...

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

[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!

In article <[email protected]>,
Spreadsheet <[email protected]>
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

[JMB]

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.


"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
>
>

[JMB]

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
>
>

[Spreadsheet]

THANKS JMB, your suggestion worked well.