Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
Quote:
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
__________________
Asobi Wa Owari Da Last edited by wickedchew : February 27th 11 at 11:59 AM Reason: Additional info |
#3
|
|||
|
|||
Quote:
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. |
#4
|
|||
|
|||
Quote:
The formula should only contain (as for the example): 1 - (Target - Actual)/Target or 1 - (Actual - Target)/Target
__________________
Asobi Wa Owari Da |
#6
|
|||
|
|||
__________________
Asobi Wa Owari Da |
#7
|
|||
|
|||
__________________
Asobi Wa Owari Da |
#8
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Average | Excel Discussion (Misc queries) | |||
Calculating a weighted average in a pivot table | Excel Discussion (Misc queries) | |||
weighted average | Excel Discussion (Misc queries) | |||
calculating a weighted average using formula | Excel Worksheet Functions | |||
calculating a weighted average uisng formula | Excel Worksheet Functions |