How to calculate the median of sums ignoring zeros?

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
591
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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 ?
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0
. . . 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 ?
 
Upvote 0
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.
 
Upvote 0
Assuming that each of the three cells occurs consecutively, try...

A2:C10

68.5
9
-4
-4
8
-4
3
5
-2

<tbody>
</tbody>

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!
 
Upvote 0
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)

:cool:


Greetings



Christian
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top