Calculating Weighted Average
 

I want to calculate a single weighed average (a "Response Index") from three email disposition metrics:

The averages for all campaigns (and thus "Response Index" = 100):
Open Rate 26.3% (a number higher than this is better)
Click Rate 3.4% (a number higher than this is better)
Unsubscribe Rate 0.02% (a number LOWER than this is better)

I would like the "Response Index" to represent these weights:
45% Open Rate
45% Click Rate
10% Unsubscribe Rate

Sample Campaign #1:
Open Rate 20.1%
Click Rate 4.1%
Unsubscribe Rate 0.00%

How would I calculate the Response Index for Sample Campaign 1? This does not work: 20.1%/26.3%*45 + 4.1%/3.4%*45 + 0.00%/0.02%*10

Thanks for your help!!

Here's a screen shot of what you need. This will calculate your Weighted Average.

Conditions of the formula:
1. If Open Rate is greater than or equal to the target (26.30%), it gets the whole weighted score (45.00%). If not, it will compute how far the raw score from the target score then multiplied from the weighted score.

2. If Click Rate is greater than or equal to the target (3.40%), it gets the whole weighted score (45.00%). If not, it will compute how far the raw score from the target score then multiplied from the weighted score.

3. If Unsubscribe Rate is lower than or equal to the target (0.02%), it gets the whole weighted score (10.00%). If not, it will compute how far the raw score from the target score then multiplied from the weighted score.

This is called the VARIANCE formula.

The HIGHER the number from the target, the HIGHER the sco
WT = 1 - (Target - Actual)/Target

The LOWER the number from the target, the HIGHER the sco
WT = 1 - (Actual - Target)/Target

Thank you wickedchew!
This is brilliant!

If I wanted to do the below, is that possible? This assumes the Response Index can be greater than 100 (but never less than 0):
If Open Rate or Click rate is above the average, I'd like for the amount each is above the average to contribute more to the index (thus each can provide more than 45 to the Response Index). And if the Unsubscribe rate is below the average, I'd like for the amount it is below the average to contribute more to the index (thus it can provide more than 10 to the Response Index).

The sample could be: Open Rate 28.00%, Click Rate 4.10%, Unsubscribe Rate 0.01%. Averages remain the same as the earlier sample.

Yes, that is possible.

The formula should only contain (as for the example):

1 - (Target - Actual)/Target

or

1 - (Actual - Target)/Target

OK, thanks! I believe this is it:

You got that right!

You got that right!

Yeah! Thank you very much!!