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!  Reply With Quote

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 ?  Reply With Quote

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!  Reply With Quote

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.  Reply With Quote

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)))  Reply With Quote

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.  Reply With Quote

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 ?  Reply With Quote

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.  Reply With Quote

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))))
Adjust the range accordingly.

Hope this helps!  Reply With Quote

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  Reply With Quote

User Tag List

Tags for this Thread

array, entered, sums, work, zeros  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•