Median IF - age groups

[Dubs]

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

 

[Hui]

Dubs

Can you attach an actual file highlighting the issue?

 

[Dubs]

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

 

[Hui]

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 ?

 

[Hui]

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

 

[Dubs]

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?

 

[Hui]

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

 

[NARAYANK991]

Hi ,

If we go by the definition and the calculations given here :

https://en.wikipedia.org/wiki/Weighted_median

then the weighted median comes out to be 26 years.

Narayan

 

[Dubs]

yes, it seems like weighted median is the better term for what I am seeking.
thanks

 

[NARAYANK991]

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

 

[Dubs]

Wow, thats crazy, thanks for everyones assistance

Dubs