0

OK. So I am setting up a business which sells 7 different plans based on affordability and circumstances.

I wish to input customer information into an Excel spreadsheet and the spreadsheet to tell me the Plan.

However basic IF equations are not working.

So for eg. If Customer has credit rating of more than 550, 0 arrears, wants cover of more than £20000 but equal to £25000 or less and has 2 or fewer insurance policies then = Plan 7.

=IF(AND(A1>550,B1>0,C1>20000,C1<=25000,D1<=2),"7")

(this bit is fine as it tells me "PLAN 7").

However.

If Customer has credit rating of less than 550 but more than 500, 0 arrears, wants cover of more than £15000 but £20000 or less and has 2 or fewer insurance policies then = Plan 6.

This equation just returns, "FALSE" if I put those matching variables in the appropriate cells: as in:

=IF(AND(A1>550,B1>0,C1>20000,C1<=25000,D1<=2)"7",IF(AND(A1>500,A1<550,B1>0,C1>15000,C1<=20000,D1<=2),"6"))

= FALSE (if information in spreadsheet should return PLAN 6) but continues to give me PLAN 7 if information in spreadsheet shoukd return PLAN 7.

I obviously wish to continue this with the 5 other parameters, until "PLAN 1".

What am I doing wrong?

Thanks so much. At wits end.

Apackman
  • 1
  • 1
  • 1
    This could be simplified with `And()`: `IF(AND(A1>550,B1>0,C1>20000,C1<=25000,D1<=2), 7, IF(AND(....), "6", "Something else"))` The reason yours is failing is because you have `IF(A1>500,IF(A1<500` It can't both be greater than 500 and less than 500 at the same time. – JNevill May 01 '18 at 18:06
  • Thanks mate. I made a mistake. It should say IF(A1>500, IF(A1<550. Sorry. Any ideas? – Apackman May 01 '18 at 18:08
  • Read it again: `IF(A1>500,IF(A1<500` You use 500 both times. It can't be below 500 and above 500 at the same time. – JNevill May 01 '18 at 18:09
  • Yup made a mistake. Just corrected. Sorry. Any ideas? – Apackman May 01 '18 at 18:10
  • It's hard to say. `If` takes three parameters. The last is what to do if the condition is `false`. If none is given it just returns "false". So we really don't know where this is failing. You could give it something to report out on so you can diagnose. For that last big IF(IF(IF(... block you could give it things to report: `IF(A1>500,IF(A1<550,IF(B1>0,IF(C1>15000,IF(C1<=20000,IF(D1<=2,"6", "failed d1 condition"),"failed c1<=20000 condition"),"failed c1>15000 condition"),"Failed a1<550 condition"), "Failed a1>500 condition") ` – JNevill May 01 '18 at 18:14
  • The formula you have typed in here won't even run since it's missing a bunch of parantheses, so maybe there is something else that we aren't seeing that is cut off that is causing this? – JNevill May 01 '18 at 18:15
  • At any rate, I would rewrite using `AND()` so it's easier to parse what's happening here. It will greatly help as you add plans to this stinker too. – JNevill May 01 '18 at 18:15
  • Hi JNevill. I didn't understand the "diagnose" message. Not due tobyour inability to explain but because my level of Excel geekery is above average but still short. However. I will edit it with correct parenthesis now. To see if you can run it. Great tip re "AND". Thanks – Apackman May 01 '18 at 18:18

1 Answers1

0

Instead of having a long complicated IF statement with hardcoded values, I instead highly recommend creating a lookup table, something like this (you can keep expanding this until you have all of your plan criteria covered for all of your plan numbers):

Plan Criteria screenshot

And then let's pretend your customer table looks like this:

Customer data screenshot

Then use a SUMIFS formula to get the plan number you're looking for (important to note that we can only use SUMIFS because we are returning a number and not text). Using this example data, put this formula in cell F2 and copy down:

=SUMIFS(P:P,H:H,"<="&B2,I:I,">="&B2,J:J,"<="&C2,K:K,">="&C2,L:L,"<="&D2,M:M,">="&D2,N:N,"<="&E2,O:O,">="&E2)

That will give us the correct plan # based on the customer data:

results

This will make your plan criteria easier to maintain and update instead of being hardcoded in your formula, and it will make future maintenance easier and more understandable.

tigeravatar
  • 26,199
  • 5
  • 30
  • 38
  • That's amazing. Thanks so much for the time you took. The problem is that I will be making this decision as I speak to the individual customer on the phone before informing them what we can offer the. I am currently putting parameters to plan manually, but wish to automate the decision process per individual customer somewhat. I appreciate your advice as a general note and have bookmarked it for reference when it becomes relevant. But it actually doesn't help me here. Or have I read you wrong? – Apackman May 01 '18 at 18:39
  • Could I do that for one single customer? I don't see the solutions in the equation so it confuses me slightly. – Apackman May 01 '18 at 18:50
  • The formula in cell F2 is looking at a single customer, Customer1 in the example, and it comparing cells B2, C2, D2, and E2 for that customer to the Plan Criteria table. If you look at the formula in F3, those references would be B3, C3, D3, and E3 for Customer2 – tigeravatar May 01 '18 at 18:55
  • So you would just adjust the values in B2, C2, D2, and E2 if you just wanted to perform the lookup for a single customer – tigeravatar May 01 '18 at 18:57
  • This is amazingly informative. If I have to do it this way, because there is no other way, then so be it. Still fantastic tool and advice. However, Is there ANY way of achieving what I wish to achieve with IF(AND? I'd rather not use a "workings table" if there's any way I could avoid it. – Apackman May 01 '18 at 19:03
  • You would need to hardcode the parameters into your IF(AND formula, given only these two plan numbers, it would look like: `=IF(AND(B2>550,B2<=850,C2=0,D2>20000,D2<=25000,E2>=0,E2<=2),7,IF(AND(B2>500,B2<=550,C2=0,D2>15000,D2<=20000,E2>=0,E2<=2),6,"no plan defined"))` – tigeravatar May 01 '18 at 19:09
  • Thank you so much. Your idea is more efficient in that plans will be constantly changing over time depending on ever changing risk assessment, appetite etc. I think I have to work out how to incorporate this. Going on to your final answer, Is the lack of "no plan defined" within the IFAND equation, what is causing the result "FALSE" to appear? – Apackman May 01 '18 at 19:11
  • There shouldn't be any possiblity that FALSE would be the formula result. All false possibilities are accounted for in the provided formula. If you modified it, please provide the modified formula for troubleshooting. – tigeravatar May 01 '18 at 19:13