Weighted Average

[FM1]

I have 3 sets of data for two different groups:

Group 1 - Inbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

Group 2 - Outbound
- Total volume
- Gross adds
- Win rate (gross adds/total volume)

I need to calculate the weighted average of the win rate based on volume of calls. Is there any way to do that?

[Palmetto]

It would be best if you uploaded a sample workbook with some representative (non-senseitive) data.

Weighted Average in Excel Spreadsheets

On this forum: Weighted Avg

[FM1]

I have had a look at the sumproduct formula but it wont work as it is an array formula and the values are in different tables in the workbook (this cannot be changed).

I have uploaded a sample sheet if it helps.

Thanks.

[Palmetto]

Are you sure you need to calculate a weighted average?
There isn't enough data in your workbook to peform such a calculation.

Weighted averages typically look a mix of related items and determine the ratio each item to the whole in order to apply a weight factor. Your data only consists, at least as given, of one data type (i.e. "category"), which makes calcualting a weighted average impossible.

Perhaps you should review/reconsider what type of measure you really need to achieve.

[FM1]

Are you sure you need to calculate a weighted average?
There isn't enough data in your workbook to peform such a calculation.

Weighted averages typically look a mix of related items and determine the ratio each item to the whole in order to apply a weight factor. Your data only consists, at least as given, of one data type (i.e. "category"), which makes calcualting a weighted average impossible.

Perhaps you should review/reconsider what type of measure you really need to achieve.

I am trying to get a weighted average of the win rate based on 'volume of calls'. In the sheet, the top table (inbound) has smaller volume with a greater win rate and the bottom table (outbound) has a greater volume with a lesser win rate.

So based on those numbers, will it be possible to get a weighted average on the win rate based on volume?

Edit - For example if the top table (inbound) has a volume of calls as 0 and the win rate as 0 and the bottom table (outbound) has a volume of calls of 1000 and win rate of 5% the weighted average would be 5% as there is no activity in inbound.

[FM1]

Edit - Im an idiot. Have found the solution - just over complicated things in my mind.

[Palmetto]

Edit - For example if the top table (inbound) has a volume of calls as 0 and the win rate as 0 and the bottom table (outbound) has a volume of calls of 1000 and win rate of 5% the weighted average would be 5% as there is no activity in inbound.

This would not be a weighted average - it would be the difference between two averages.

Based the explanation in your previous post:

A weighted average, per your data, would be calculated seperately for inbound and outbound and the formula structure is the same:

Inbound Acquired / (Inbound Calls + Outbound Calls)
or
Outbound Acquired / (Inbound Calls + Outbound Calls)

To obtain a overall average of the Win rate based on total acquired per total volume, then:

(Inbound Acquired + Outbound Acquired) / (Inbound Volume + Outbound Volume)

To obtain a Grand Average of the Win rate:
(Inbound Win Rate + Outbound Win Rate) / 2

See Attached.

Have found the solution - just over complicated things in my mind.

Please post your solution so that others finding this thread will have an answer to a similar problem.