Is it possible to simplify these chained IF statements without lookup table?

[strangebiscuit]

Pardon, I know this is rather confusing...I'm just having trouble thinking of how to simplify this and wondered if anyone had any ideas they'd care to share.

I started writing a formula recently with a bunch of nested IF statements that's kind of spiraled out of control. I'm wondering if anyone has any ideas of how to simplify it without the need for creating a separate sheet and using VLOOKUP.

Basically, this is supposed to calculate a payment amount based on number of points earned in B36 and number of days in attendance (maximum of "6") in B37 and place it in the cell below - B38. So once again, points in B36, attendance in B37, result in B38.

While the points total in B36 is less than 10:

• If the attendance in B37 is 6 (the maximum)...the total in B38 should be 250.
• If B37 is 5...the total (B38) should be 210
• If B37 is 4...the total (B38) should be 168.
• If B37 is 3...the total (B38) should be 126.
• If B37 is 2...the total (B38) should be 84.
• If B37 is 1...the total (B38) should be 42.

Then when points total in B36 is more than 10...there are a series of tiers it can fall into where, if attendance (B37) is at the maximum of 6...the total in B38 should be raised. In all of these tiers, 40 is subtracted from total in B38 each time attendance (B37) drops by 1. So for example:

While the points total in B36 is between 10 and 14:

• If the attendance in B37 is 6 (the maximum)...the total in B38 should be 350.
• If B37 is 5...the total (B38) should be 310...(350-40)
• If B37 is 4...the total (B38) should be 270.
• If B37 is 3...the total (B38) should be 230.
• If B37 is 2...the total (B38) should be 190.
• If B37 is 1...the total (B38) should be 150.

The other tiers are as such:

• B36 between 15 and 19 and B37 = 6: 400
• B36 between 20 and 24 and B37 = 6: 450
• B36 25 or more and B37 = 6: 500

I chained a couple of nested IF statements together when it was just two tiers, but as the additional tiers were added it's gotten very messy and I believe one more tier would put it over the maximum value count for XLS. Here's the horrible jumble of IF statements as they stand now:

=IF(OR(B36<0,B37=0),0,IF(AND(AND(B36<10),B37=6),250,IF(AND(B36<10,B37=5),210,IF(AND(B36<10,B37=4),168,IF(AND(B36<10,B37=3),126,IF(AND(B36<10,B37=2),84,IF(AND(B36<10,B37=1),42,0)))))))+IF(AND(AND(B36>=10,B36<15),B37=6),350,IF(AND(AND(B36>=10,B36<15),B37=5),310,IF(AND(AND(B36>=10,B36<15),B37=4),270,IF(AND(AND(B36>=10,B36<15),B37=3),230,IF(AND(AND(B36>=10,B36<15),B37=2),190,IF(AND(AND(B36>=10,B36<15),B37=1),150,0))))))+IF(AND(AND(B36>=15,B36<20),B37=6),400,IF(AND(AND(B36>=15,B36<20),B37=5),360,IF(AND(AND(B36>=15,B36<20),B37=4),320,IF(AND(AND(B36>=15,B36<20),B37=3),280,IF(AND(AND(B36>=15,B36<20),B37=2),240,IF(AND(AND(B36>=15,B36<20),B37=1),200,0))))))+IF(AND(AND(B36>=20,B36<25),B37=6),450,IF(AND(AND(B36>=20,B36<25),B37=5),410,IF(AND(AND(B36>=20,B36<25),B37=4),370,IF(AND(AND(B36>=20,B36<25),B37=3),330,IF(AND(AND(B36>=20,B36<25),B37=2),290,IF(AND(AND(B36>=20,B36<25),B37=1),250,0))))))+IF(AND(B36>=25,B37=6),500,IF(AND(B36>=25,B37=5),460,IF(AND(B36>=25,B37=4),420,IF(AND(B36>=25,B37=3),380,IF(AND(B36>=25,B37=2),340,IF(AND(B36>=25,B37=1),300,0))))))IF(AND(B36>=25,B37=6),500,IF(AND(B36>=25,B37=5),460,IF(AND(B36>=25,B37=4),420,IF(AND(B36>=25,B37=3),380,IF(AND(B36>=25,B37=2),340,IF(AND(B36>=25,B37=1),300,0))))))

I certainly understand if this is too much for anyone to fathom, and realize the obvious suggestion is to use a lookup table on another sheet...again, just wondering if anyone else had any other methods that might work. It has to be live updating so putting the whole thing into a regular VBA macro wouldn't work.

Thanks very much in advance for anyone who takes the trouble to even look at this!

 

[FDibbins]

Did you see my post #7?

 

[DRSteele]

[I see my post above didn't have the table included; I'm having quite the struggle using this forum text editor with MS Edge.]

Here is a lookup table:

	A	B	C	D	E	F	G	H	I	J	K

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #DDEBF7"]LOOKUP TABLE[/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]
[TD="bgcolor: #DDEBF7, align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="bgcolor: #FCE4D6"]points[/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FCE4D6, align: right"]0[/TD]
[TD="bgcolor: #FCE4D6, align: right"]11[/TD]
[TD="bgcolor: #FCE4D6, align: right"]15[/TD]
[TD="bgcolor: #FCE4D6, align: right"]20[/TD]
[TD="bgcolor: #FCE4D6, align: right"]25[/TD]
[TD="bgcolor: #FCE4D6"]1E+308[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]350[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]500[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]310[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]attendance[/TD]
[TD="bgcolor: #FFF2CC, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="bgcolor: #FCE4D6"]points[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="bgcolor: #FFF2CC"]attendance[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #C6E0B4"]RESULT[/TD]
[TD="bgcolor: #C6E0B4, align: right"]400[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"][/TD]
[TD="bgcolor: #FFF2CC, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]K32[/TH]
[TD="align: left"]= 9.99999999999999E+307[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B38[/TH]
[TD="align: left"]=INDEX(G33:K38,MATCH(B37,E33:E38,0),MATCH(B36,F32:K32,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[strangebiscuit]

Did you see my post #7?

Yeah I did, I think you're onto something clever with it...Hercules1946 suggested something similar as well.

Modified for my purposes it would be something like: =IF(B36<10,B37*42,IF(B36<15,150+40*(B37-1),IF(B36<20,200+40*(B37-1),IF(B36<25, 250+40*(B37-1), 300+40*(B37-1)))))

This almost works except for two things...
1.For the < 10 tier if attendance (B37) is 6...the total should be 250. This evaluates to 252 is this instance.
2. If attendance (B37) <= 0 in any tier the total should be 0.

 

[DRSteele]

You can dispense with the lookup table by hard-coding it into the formula; put this in B38:

=INDEX({250,350,400,450,500;210,310,0,0,0;168,270,0,0,0;126,230,0,0,0;84,190,0,0,0;42,150,0,0,0},MATCH(B37,{6;5;4;3;2;1},0),MATCH(B36,{0,11,15,20,25,9.99999999999999E+307},1))

 

[FDibbins]

Yeah I did, I think you're onto something clever with it...Hercules1946 suggested something similar as well.

Modified for my purposes it would be something like: =IF(B36<10,B37*42,IF(B36<15,150+40*(B37-1),IF(B36<20,200+40*(B37-1),IF(B36<25, 250+40*(B37-1), 300+40*(B37-1)))))

This almost works except for two things...
1.For the < 10 tier if attendance (B37) is 6...the total should be 250. This evaluates to 252 is this instance.
2. If attendance (B37) <= 0 in any tier the total should be 0.

OK, I missed the celing, adjusted to this...
=IF(A2>0,IF(A1<10,MIN(250,A2*42),MIN(350,150+40*(A2-1))),"")

 

[strangebiscuit]

Nice! I've now got two viable solutions that do exactly what I require!

One based on FDibbins's (and Hercules1946's) suggestions:

=IF(B37<=0,0,IF(B36<10,MIN(250,B37*42),IF(B36<15,MIN(350, 150+40*(B37-1)),IF(B36<20,MIN(400, 200+40*(B37-1)),IF(B36<25, MIN(450, 250+40*(B37-1)), MIN(500, 300+40*(B37-1)))))))

And another based on DRSteele's hard-coded lookup table suggestion:

=INDEX({250,350,400,450,500;210,310,360,410,460;168,270,320,370,420;126,230,280,330,380;84,190,240,290,340;42,150,200,250,300;0,0,0,0,0},MATCH(B37,{6;5;4;3;2;1;0},0),MATCH(B36,{-1000,10,15,20,25,9.99999999999999E+307},1))

Both work pretty much perfectly for my purposes. The first one (IF) is a bit shorter and maybe a little easier to read, but the second (INDEX) would be more flexible if I need to apply it to a similar structure that's got a less straightforward mathematical pattern.

Thanks so much to everyone who replied...I learned a lot from this. This forum is a total godsend.

 

[FDibbins]

Happy to help

 

[DRSteele]

I too was happy to help. Best of luck!

 

[Hercules1946]

For all the tiers after the "points < 10" group (so tier 2 - 5) it's supposed to be that each time attendance (B37) goes down by 1, 40 is subtracted ...

So... just to be clear, my formula in #9 needs amending so that tiers 3, 4, and 5 for each drop in attendance below 6, 40 needs to be subtracted? e.g. if B37 = 3, then:
Tier 3 =280, Tier 4 = 330 and Tier 5 = 380.

If all that is correct then this formula should do it:
=IF(B36<10, 42*B37,IF(B36<=14, ((B37-1)*40)+150,IF(B36<=19,((B37-1)*40)+200,IF(B36<=24,((B37-1)*40)+250,IF(B36>=25,((B37-1)*40)+300,"")))))
Just another thought .... If you have a spare cell to hold the formula ((B37-1) * 40) the main formula would be much simpler.
Let me know how you get on with it.

 

[Hercules1946]

J.. If you have a spare cell to hold the formula ((B37-1) * 40) the main formula would be much simpler.
Let me know how you get on with it.

My last effort This sorts out the 250 ceiling, and simplifies by holding the repeated expression in A1 (e.g.)

=IF(B36<10, MIN(42*B37,250),IF(B36<=14, $A$1+150,IF(B36<=19,$A$1+200,IF(B36<=24,$A$1+250,IF(B36>=25,$A$1+300,"")))))