MTD Rating
 

I have a monthly number of complaints. I need to compute a MTD average of
these complaints and rate them as excellent, good, fair. The problem is that
the rating scale changes based on the month

Month Complaints Excellent Good Fair
Jan 2008 20 <=20 21 - 30 30
Feb 2008 30 <=20 21 - 30 30
Mar 2008 40 <=10 11 - 25 26
Apr 2008 <=10 11 - 25 26
May 2008 <=10 11 - 25 26

I need to compute a MTD Rating of complaints only for those months that have
complaints recorded -in this case Jan-Mar 2008 (data for Apr onwards is not
yet available). I require this formula to be in one cell (hopefully).

Please help!

MTD Rating
 

Assuming your months are in column A, complaints in column B
In column C:E, we'll place the boundaries for the ranking for each month.
In column C, always put 0.
In column D, put the lowest number that would equal "Good"
In column E, the lowest number that would equal "Fair" (for your example of
Jan, would be 30.01). Note that you decimate as far as you need, and could
set format of cell to only display integer.

In column F:
=IF(ISBLANK(B2),"",LOOKUP(AVERAGE($B$2:B2),C2:E2,{ "Excellent","Good","Fair"}))
You can copy this cell down as needed.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"reddy" wrote:

I have a monthly number of complaints. I need to compute a MTD average of
these complaints and rate them as excellent, good, fair. The problem is that
the rating scale changes based on the month

Month Complaints Excellent Good Fair
Jan 2008 20 <=20 21 - 30 30
Feb 2008 30 <=20 21 - 30 30
Mar 2008 40 <=10 11 - 25 26
Apr 2008 <=10 11 - 25 26
May 2008 <=10 11 - 25 26

I need to compute a MTD Rating of complaints only for those months that have
complaints recorded -in this case Jan-Mar 2008 (data for Apr onwards is not
yet available). I require this formula to be in one cell (hopefully).

Please help!

MTD Rating
 

Luke, Thank you for the post. It worked!

"Luke M" wrote:

Assuming your months are in column A, complaints in column B
In column C:E, we'll place the boundaries for the ranking for each month.
In column C, always put 0.
In column D, put the lowest number that would equal "Good"
In column E, the lowest number that would equal "Fair" (for your example of
Jan, would be 30.01). Note that you decimate as far as you need, and could
set format of cell to only display integer.

In column F:
=IF(ISBLANK(B2),"",LOOKUP(AVERAGE($B$2:B2),C2:E2,{ "Excellent","Good","Fair"}))
You can copy this cell down as needed.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"reddy" wrote:

I have a monthly number of complaints. I need to compute a MTD average of
these complaints and rate them as excellent, good, fair. The problem is that
the rating scale changes based on the month

Month Complaints Excellent Good Fair
Jan 2008 20 <=20 21 - 30 30
Feb 2008 30 <=20 21 - 30 30
Mar 2008 40 <=10 11 - 25 26
Apr 2008 <=10 11 - 25 26
May 2008 <=10 11 - 25 26

I need to compute a MTD Rating of complaints only for those months that have
complaints recorded -in this case Jan-Mar 2008 (data for Apr onwards is not
yet available). I require this formula to be in one cell (hopefully).

Please help!