MTD Rating

[reddy]

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!

 

[FloMM2]

reddy,
I have an answer for you. Assuming Month is in cell A2, and the months are
in Cells A3 thru A7.
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 >25
Apr 2008 <=10 11 - 25 >25
May 2008 <=10 11 - 25 >25
Note: The changes to last column for Mar 2008,Apr 2008, and May 2008.
I added a column after "Fair" that is "MTD". In the first cell under "MTD"
is the formula: "=IF(B3<=20,"Excellent",IF(21<B3<30,"Good","Fair"))"
The next cell down (F4) formula:
"=IF((SUM(B3:B4)?2<=20,"Excellent",IF(21<(Sum(B3:B4)/2)<30,"Good","Fair"))"
The next cell down (F5)
formula:"=IF((SUM(B3:B5)/3)<=10,"Excellent",IF(11<(SUM(B3:B5)/3)<25,"Good","Fair"))"
What this does is take the total of score divide it by the number of months
(so far), compares it to the line for that month, and give results Excellent,
Good, or Fair.
If you like what I also added to the front of the formula for Apr 2008's MTD
cell (F6)
"IF(B6="","",IF((SUM(B3:B6)/4)<=10,"Excellent",IF(((SUM(B3:B6)))/4)<=25,"Good","Fair")))"
This will keep the cell in the MTD column empty until something is put in
the cell for that month.

hth
Dennis