Solved Please help me make if, then statements!

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


See More: Please help me make if, then statements!

Report •


#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
✔ Best Answer
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.


Report •

Related Solutions


Ask Question