# Thread: How to calculate the median of sums ignoring zeros? Thanks: 0 Likes: 0

1. ## How to calculate the median of sums ignoring zeros?

Hello

I have the formula =MEDIAN(SUM(A1,A2),SUM(A3,A4)) and I want to ignore the zeros if any of the SUMs is zero.

How can I do this please?

I tried =MEDIAN(IF(SUM(A1,A2)<>0,SUM(A1,A2)),IF(SUM(A3,A4)<>0,SUM(A3,A4))) entered as ARRAY but it did not work.

Thanks!

2. ## Re: How to calculate the median of sums ignoring zeros?

So what do you want the result to be, IF one of the SUMs IS actually zero ?

For example, let's say
SUM(A1,A2)=0
and
SUM(A3,A4)=10

What should the result be ?

3. ## Re: How to calculate the median of sums ignoring zeros?

Originally Posted by Gerald Higgins
So what do you want the result to be, IF one of the SUMs IS actually zero ?

For example, let's say
SUM(A1,A2)=0
and
SUM(A3,A4)=10

What should the result be ?
It should then be 10 not 5!

4. ## Re: How to calculate the median of sums ignoring zeros?

The median is the middle. With an even number of values (e.g., A3 and A4), the median is .5 of the sum.
So, if the sum = 10, the values might be 2+8 which makes the median 5; if the sum = 10, the values might be -2 and 12 and the median = 5.

5. ## Re: How to calculate the median of sums ignoring zeros?

Is this what you are after?

=IF(OR(SUM(A1:A2)=0, SUM(A3:A4)=0), SUM(A1:A4), MEDIAN(SUM(A1,A2),SUM(A3,A4)))

6. ## Re: How to calculate the median of sums ignoring zeros?

Originally Posted by Finalfight40
Is this what you are after?

=IF(OR(SUM(A1:A2)=0, SUM(A3:A4)=0), SUM(A1:A4), MEDIAN(SUM(A1,A2),SUM(A3,A4)))
Kind of, but the SUMs are more than two, so it won't work.

7. ## Re: How to calculate the median of sums ignoring zeros?

. . . but the SUMs are more than two . . .
What does this mean ?

Do you mean you have more than two SUM formulas to derive the median from ?
In which case, how many SUM formulas do you have ?
Three ?
One hundred ?

8. ## Re: How to calculate the median of sums ignoring zeros?

Originally Posted by Gerald Higgins
What does this mean ?

Do you mean you have more than two SUM formulas to derive the median from ?
In which case, how many SUM formulas do you have ?
Three ?
One hundred ?
Unfortunately they are 45 SUMs of three cells each.

9. ## Re: How to calculate the median of sums ignoring zeros?

Assuming that each of the three cells occurs consecutively, try...

A2:C10

 6 8.5 9 -4 -4 8 -4 3 5 -2

C2, confirmed with CONTROL+SHIFT+ENTER...

Code:
`=MEDIAN(IF(SUBTOTAL(9,OFFSET(A2:A10,ROW(INDIRECT("1:"&ROWS(A2:A10)/3))*3-3,0,3))>0,SUBTOTAL(9,OFFSET(A2:A10,ROW(INDIRECT("1:"&ROWS(A2:A10)/3))*3-3,0,3))))`

Hope this helps!

10. ## Re: How to calculate the median of sums ignoring zeros?

Hi,

a little bit formular is:

=AGGREGATE(16,6,1/(1/(SUBTOTAL(9,OFFSET(A2,(ROW(INDIRECT("1:"&ROWS(A2:A10)/3))-1)*3,,3,)))),0.5)

Greetings

Christian