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 ?
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!
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 ?
The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
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.
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)))
VBA is ten percent luck
Twenty percent skill
Fifteen percent concentrated power of will
Five percent pleasure
Fifty percent pain
And a hundred percent reason to remember the name
What does this mean ?. . . but the SUMs are more than two . . .
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 ?
The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !
Sub Macro()
ActiveCell = "IY" & Right(Application.Name, 5)
With ActiveCell.Characters(Start:=2, Length:=1).Font
.Name = "Webdings"
.Color = 255
End With
End Sub
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...
Adjust the range accordingly.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!
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
Like this thread? Share it with others