1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Median IF - age groups

Discussion in 'Ask an Excel Question' started by Dubs, Nov 13, 2017.

  1. Dubs

    Dubs New Member

    Messages:
    17
    Hi team, I have a median formula that is removing zero's nicely and is providing a median age.
    {=MEDIAN(IF(E383:DA383<>0,$E$10:$DA$10))}

    The issue is it seems to also be counting the corresponding ages e10:da10 even if the corresponding row has a zero
    ie sales assistants is returning median age of 49.5 instead of around age 25
    Col DA is 115yo
    There are 487 rows so i would prefer not to do this manually

    TIA for assistance
    Hopefully to do without a macro as I know nothing about macrs's

    Attached Files:

    Last edited: Nov 13, 2017
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    Dubs

    Can you attach an actual file highlighting the issue?
  3. Dubs

    Dubs New Member

    Messages:
    17
    Yes, this is data freely available from the ABS too.

    Attached Files:

  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    You didn't highlight what the issue is?

    DD11 should be: =SUMIF(E11:DA11,"<="&DB11,E11:DA11)

    No idea what you're trying to do in DE11 ?
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    If as I suspect the Numbers in Columns E:DA are the number of people in each age group working in that job
    then D11 should be:
    =MEDIAN(IF(E11:DA11<>0,($E$10:$DA$10*E11:DA11)/E11:DA11)) Ctrl+Shift+Enter
    Thomas Kuriakose likes this.
  6. Dubs

    Dubs New Member

    Messages:
    17
    Yes you were correct regarding the number of persons by age in each job and given that I am seeking a median age of persons employed in each role.
    Thanks for your help. Col DC onwards was testing.

    the formula for D11 gives the same result as my original formula -

    does the bold portion of your formula $E$10:$DA$10*E11:DA11)/E11:DA11 cancel itself out?
  7. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    No
    It weights the Number of workers by the age, to give you a weighted average age to which median applies

    If you copy it down it fixes the issues in lower cells
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Thomas Kuriakose likes this.
  9. Dubs

    Dubs New Member

    Messages:
    17
    yes, it seems like weighted median is the better term for what I am seeking.
    thanks
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    See the attached file.

    There is still a problem or two ; I have seen that in the case of row 323 , the MATCH function , which has been used with a third parameter of 1 , returns the correct value , but the formula , which does not see an exact match anywhere else , has not been made for this. It can be revised , but I don't think a case or two like this matters.

    If you want the formula to be revised , let me know.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  11. Dubs

    Dubs New Member

    Messages:
    17
    Wow, thats crazy, thanks for everyones assistance

    Dubs

Share This Page