Median calculation and ignore zeros

[Guest]

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?

 

[Guest]

try something lik
=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,countif(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))

 

[Guest]

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

 

[Guest]

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

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

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?

 

[Guest]

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

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

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?

 

[Guest]

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


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

XL2002, WinXP

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

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

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?