How to calculate the median of sums ignoring zeros?

excelos

Active Member
Joined
Sep 25, 2011
Messages
356
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!
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

Active Member
Joined
Sep 25, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 8, 2018
Messages
1,443
Office Version
  1. 365
  2. 2010
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

Active Member
Joined
Apr 24, 2018
Messages
273
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115

ADVERTISEMENT

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

Active Member
Joined
Sep 25, 2011
Messages
356
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
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!
 

Besserwisser

New Member
Joined
Jul 30, 2015
Messages
17
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,938
Messages
5,598,959
Members
414,269
Latest member
FJXMTT

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
Top