Weighted averages

Resolved · Medium Priority · Version 2003

Tue 4 Aug 2009, 13:16 Delegate Gale said...

Gale has attended:
Excel Intermediate course
Excel Advanced course

Weighted averages

Good afternoon,

I'm trying to work out how to let Excel calculate a weighted average on something.

In simple terms, I have a list of customers, their current differential at a certain location and their contracted volume.

I would like to determine the average differential per unit. So - for example.

Customer A has a differential of 3.50 for 1,000,000 units.
Customer B has a differential of 6.57 for 57,888 units.
Customer C has a differential of 10.28 for 3,434 units.

How can Excel help me determine the average differential we're charging per unit?

Many thanks if you can help.

Fri 7 Aug 2009, 09:23 Trainer Stephen said...

RE: Weighted averages

Hi Gale

Thank you for your question

The solution is to make use of the SUMPRODUCT function. This function multiplies two ranges together and then returns the sum of those products.
You would apply the sum product function to the ranges containing the values and thier weights and then divide that by the sum of the weights.

I have enclosed an example file for your information

Regards

Stephen