Help with Nested IF Function

[powerpivotlegal]

Hello,

I have combined the 2 sample tables below in PowerPivot and am trying to calculate the correct weighted sum for each Grouping (Partner, Associate, Paralegal). However, just doing a sum of Weighted Avg Rate (Avg Rate x Weighting) will return an incorrect sum for a grouping that does not have weighted rates for certain titles.

For example, there are 3 allocations (48% for Principal, 5% for Of Counsel and 47% for Local Partner) for the Grouping Partner, but Siberia only has 2 titles/rates. The sum of these weighted avg rates equals 376 which is undervaluing the Partner rate because it is not factoring in the missing 5% allocation of the non-existent position "Of Counsel".

How can PowerPivot analyze which titles and allocations are missing for each country (i.e. Weighting does not equal 100% for group), take the average rate and readjust the weighting to account for the missing allocations?

Country Title Average Rate Weighted Avg Rate
Siberia Principal 465.00 223.20
Siberia Of Counsel - -
Siberia Local Partner 325.00 152.75
Siberia Senior Associate 250.00 62.50
Siberia Mid-Level Associate 240.00 60.00
Siberia Junior Associate 150.00 37.50
Siberia Trainee - -
Siberia Paralegal 100.00 25.00

Title Grouping Weighting
Principal Partner 48%
Of Counsel Partner 5%
Local Partner Partner 47%
Senior Associate Associate 25%
Senior Associate / Counsel Associate 25%
Mid-Level Associate Associate 25%
Junior Associate Associate 25%
Trainee Paralegal 25%
Paralegal Paralegal 25%
Paralegal / Translator / Others Paralegal 25%
Paralegal / Translator Paralegal 25%


Many thanks!

 

[scottsen]

Divide by the Sum of the Weightings? (so, typically it would just divide by 1, but Siberia would divide by 0.95 which would bump up the value)