How to calculate the median of sums ignoring zeros?

[excelos]

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!

 

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

 

[excelos]

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!

 

[kweaver]

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.

 

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

 

[excelos]

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.

 

[Gerald Higgins]

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

 

[excelos]

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.

 

[Domenic]

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

A2:C10

[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]6[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64, align: right"]8.5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-4[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]-2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

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

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

Adjust the range accordingly.

Hope this helps!

 

[Besserwisser]

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