Weighted median

  • I need to calculate the weighted median for the average number of applicants per vacancy by state. The problems is that we only want the median for those records which have results for both the number of vacancies and number of applicants.


    For example:
    State Number of vacancies Number of applicants
    NSW 70 0
    VIC 54 0
    QLD 50 70
    NSW 50 0
    VIC 45 45
    QLD 40 0
    NSW 40 200

  • Re: Weighted median


    Do you actually mean weighted median (for those only with applicants and vacancies) or really do mean weighted median of the averages? If the latter is the case what do you use as the weighting for the averages? Based on your answers is a VBA solution acceptable?

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Weighted median


    [FONT=&quot]Oooh, so very confusing! It’s the weighted median of the average number of applicants per vacancy – so calculate the average number of applicants per vacancy for each record (and blank if they didn’t answer all the questions properly) and then calculate the median weighted for the number of vacancies.[/FONT]
    [FONT=&quot] [/FONT]
    [FONT=&quot]Here’s an example using 3 records (ie 3 employers).[/FONT]
    [FONT=&quot]Number of vacancies Average number of applicants per vacancy[/FONT]
    [FONT=&quot]1 10[/FONT]
    [FONT=&quot]1 15 [/FONT]
    [FONT=&quot]3 5[/FONT]
    [FONT=&quot] [/FONT]
    [FONT=&quot]As this is weighted for the number of vacancies, the median is 5 (without weighting, it would be 10).[/FONT]
    [FONT=&quot] [/FONT]
    [FONT=&quot]I’m very happy to have a new function defined using VBA or creating as many new columns as you want, but I don’t want this to be a macro that requires button pushing if at all possible.[/FONT]

  • Re: Weighted median


    A UDF will do this - just so I am clear your original post says

    Quote

    weighted median for the average number of applicants per vacancy by state


    Does that mean you want the weighted median of the average for EACH state - so in effect there will be a result for each state. In you last post for example - that would be the calculation for ONE state?
    Or have State as a parameter in the UDF along with the range containing the data
    eg = WeightMedAvg(state,range)


    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Weighted median


    The ideal would be somethinglike highlighting the whole sheet and having a formula like:
    WeightMedAvg(State,NSW,NumVacs,AveApps)
    Where State is the name of thevariable you want it filtered by, NSW is the state value you want, NumVacs isthe number of vacancies (this would effectively be the weight variable) andAveApps is the column with the data you want the weighted median calculatedfrom.

    However, the formula is going tobe applied in a predictable way. That means that the State, Industry, NumVacsand AveApps will be in the same columns each time. If it’s easier to justhardcode the rows in there, then I’m happy with any sort of output. Forexample, the shorter version might be:
    WeightMedAvg(NSW)
    As the State, NumVacs andAveApps are defined within the code to be in certain columns.

    I hope this helps.

  • Re: Weighted median


    The way I was intending to write this now that this is clear would be
    =WeightMedAvg(State,State Range, Vacancy Range, Applicant Range)
    State would be a string representing the state and the 3 Ranges would be the 3 columns (State, No of Vacancies, No of Applicants) - that way the 3 columns do not have to be next to each other - as long as the data for State is in 1 column, Applicants in 1 column and Vacancies in 1 column then the formula could have 4 parameters giving you the greatest flexibility in your workbook design. Prefer not to hard code in columns as it reduces portability of the UDF. I am currently at work - will write when I get home - will not take long to do.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Weighted median


    This UDF should do what you require - it will calculate the weighted median of the average number of applicants per vacancy for a specific state - it will not take into account any zero values. Vacancies is the weighting factor.
    The formula is =WeightedMedianAvg(StateRng,Vacancies_Range,Applicants_Range)
    So select the State as a single cell (as a parameter it is a range) then select the range of Vacancies, then the range of applicants
    e.g. =WeightedMedianAvg(A2,I2:I12,D2:D12) A2 contains the State, the second range is the vacancies and the third range is the applicants. I have done it this way as it means that you can have the State column, Applicant column and Vacancy column anywhere in the workbook - they do not have to be together. However the ranges of the Applicants and Vacancies need to start at the same row and end at the same row in the formula. All parameters are validated prior to carrying out calculation. If this check fails the formula will generate #Value.
    I have attached a workbook as well with some dummy data.

  • Re: Weighted median


    Excellent. Your welcome. :)

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!