Originally Posted by wickedchew

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

Originally Posted by sfmoored

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.

Originally Posted by wickedchew

Yes, that is possible.

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

1 - (Target - Actual)/Target

or

1 - (Actual - Target)/Target

Originally Posted by sfmoored

OK, thanks! I believe this is it:

Originally Posted by sfmoored

OK, thanks! I believe this is it:

Originally Posted by wickedchew

You got that right!