July 11, 2012 at 17:33:00
Specs: Windows 7
 I need to make an if, then statement on Excel for this problem, and I keep getting error messages. I am so frustrated. This is the problem:The following ratings are as follows: A, B, C, D, F. The criteria of rating is as follows:• If the ratio of complaints to new customers is less than 10%, the insurance company is graded as A, otherwise• If the ratio of complaints to new customers is less than 20%, the insurance company is graded as B, otherwise• If the ratio of complaints to new customers is less than 30%, the insurance company is graded as C, otherwise• If the ratio of complaints to new customers is less than 40%, the insurance company is graded as D, otherwise• Otherwise, the insurance company is graded as F• Also, if the amount of money paid out is less than what is received, there is an additional 5% penalty tacked on to the original ratio of complaints to new customers.The complaints are in column D4 and the customers are in column E4

#1
July 11, 2012 at 19:19:47
 Where is the data related to whether there is a 5% penalty assessed or not?Is the 5% a straight addition to the ratio or is it 5% of the ratio value that is added?This question is just a curiosity:If they have to pay out more than or even the same amount as they receive, how do they make any money?Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

#2
July 11, 2012 at 20:29:48
 Instead of writing a long Nested IF statement, I'd use VLOOKUP.That way if the ratings ever change, you only have to change the table, not any of the formulas.Start with a table that looks like this:``` A B C 1 0% A 21% 2 10% B 3 20% C 4 30% D 5 40% F ```Using this formula, you should get C because C1 contains 21%:=VLOOKUP(C1, \$A\$1:\$B\$5, 2, 1) Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.