weighted averages

[runner]

I have 30 zones that have a % contribution and I want to calculate a total % contribution by weighting each of the 30 zones for example:

% contribution
Zone 1 30%
Zone 2 40%
Zone 3 50%


Total Sales
Zone 1 1000
Zone 2 1200
Zone 3 2500


Using total sales I want to weight each zones % contribution vs adding 30% + 40% +50% /3 or 40%.

Any thoughts here?

[daddylonglegs]

The standard method for a weighted average would be

=SUMPRODUCT(Sales,Percentages)/SUM(Percentages)

which, for your example, would give approx 1692 - is that what you want?

[runner]

I am looking for the weighted average percent vs sales, would it just be the reverse of your formula?

[daddylonglegs]

Yes, you'd just replace SUM(Percentages) with SUM(Sales), that would give you approx 43%

[runner]

Perfect, thank you