Median calculation and ignore zeros
 

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal

Median calculation and ignore zeros
 

try something like
=if(large(range,countif(range,"<0")/2)0,large(range,countif(range,"<0")/2),small(range,countif(range,"<0")/2))

if you want to be more accurate,

=if(mod(countif(range,"<0"),2)=1,if(large(range,c ountif(range,"<0")/2)0,large(range,countif(range,"<0")/2),small(range,countif(range,"<0")/2))if(large(range,countif(range,"<0")/2)0,(large(range,countif(range,"<0")/2)-large(range,countif(range,"<0")/2+1))/2,(small(range,countif(range,"<0")/2)+small(range,countif(range,"<0")/2+1)/2))

"coastal" wrote:

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal

Median calculation and ignore zeros
 

Try this ARRAY FORMULA:
=MEDIAN(IF(N392:N1491<0,N392:N1491))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"coastal" wrote:

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal

Median calculation and ignore zeros
 

If you want to do it the *hard* way <bg

For values (including zeros, blanks, or text) in N392:N1491
try this regular formula:

=AVERAGE(LARGE(N392:N1491,(ROWS(N392:N1491)-SUMPRODUCT(--ISERROR(1/N392:N1491)))/2+(ISEVEN(ROWS(N392:N1491)-SUMPRODUCT(--ISERROR(1/N392:N1491))))*{0,1}))

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this ARRAY FORMULA:
=MEDIAN(IF(N392:N1491<0,N392:N1491))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"coastal" wrote:

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal

Median calculation and ignore zeros
 

This was right on the money! Thanks!
--
--coastal


"Ron Coderre" wrote:

Try this ARRAY FORMULA:
=MEDIAN(IF(N392:N1491<0,N392:N1491))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"coastal" wrote:

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal

Median calculation and ignore zeros
 

Thanks for the feedback.....I'm glad I could help.


***********
Regards,
Ron

XL2002, WinXP


"coastal" wrote:

This was right on the money! Thanks!
--
--coastal


"Ron Coderre" wrote:

Try this ARRAY FORMULA:
=MEDIAN(IF(N392:N1491<0,N392:N1491))

Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"coastal" wrote:

I am trying to calculate both an average and a median for a list of numbers.
I have zeros in the list which I want to keep in the average calculation but
need to ignore the zeros in the median calculation. Is there a way to write
that formula? Right now I have: =MEDIAN(N392:N1491)

I cannot simply sort by the column in question because I have serveal
columns that I have to do an average & a median for.

Any suggestions?
--
--coastal