Results 1 to 10 of 10

Thread: How to calculate the median of sums ignoring zeros?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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!

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate the median of sums ignoring zeros?

    Quote Originally Posted by Gerald Higgins View Post
    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!

  4. #4
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    222
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  6. #6
    Board Regular
    Join Date
    Sep 2011
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate the median of sums ignoring zeros?

    Quote Originally Posted by Finalfight40 View Post
    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.

  7. #7
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Posts
    301
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to calculate the median of sums ignoring zeros?

    Quote Originally Posted by Gerald Higgins View Post
    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.

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,855
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    5 Thread(s)

    Default 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!

  10. #10
    New Member
    Join Date
    Jul 2015
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default 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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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