Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default 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   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Wink

Quote:
Originally Posted by sfmoored View Post
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
Attached Images
 
__________________
Asobi Wa Owari Da

Last edited by wickedchew : February 27th 11 at 11:59 AM Reason: Additional info
  #3   Report Post  
Junior Member
 
Posts: 4
Thumbs up

Quote:
Originally Posted by wickedchew View Post
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.
  #4   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by sfmoored View Post
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
__________________
Asobi Wa Owari Da
  #5   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by wickedchew View Post
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:
Attached Images
 


  #6   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by sfmoored View Post
OK, thanks! I believe this is it:
You got that right!
__________________
Asobi Wa Owari Da
  #7   Report Post  
Senior Member
 
Location: Philippines
Posts: 161
Default

Quote:
Originally Posted by sfmoored View Post
OK, thanks! I believe this is it:
You got that right!
__________________
Asobi Wa Owari Da
  #8   Report Post  
Junior Member
 
Posts: 4
Smile

Quote:
Originally Posted by wickedchew View Post
You got that right!
Yeah! Thank you very much!!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weighted Average Brian Excel Discussion (Misc queries) 7 November 24th 09 07:00 PM
Calculating a weighted average in a pivot table mrogge Excel Discussion (Misc queries) 1 April 16th 09 06:15 PM
weighted average inoexcel Excel Discussion (Misc queries) 3 May 1st 06 10:03 PM
calculating a weighted average using formula bob green Excel Worksheet Functions 1 August 1st 05 10:33 PM
calculating a weighted average uisng formula bob green Excel Worksheet Functions 1 August 1st 05 06:31 AM


All times are GMT +1. The time now is 11:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"