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

S

strangebiscuit

New Member
Joined
Nov 25, 2013
Messages
35
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:

Code: 
=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!
 
Sort by date Sort by votes

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
[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:

ABCDEFGHIJK

<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]
 
Upvote 0
FDibbins said:
Did you see my post #7?
Click to expand...

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.
 
Upvote 0
You can dispense with the lookup table by hard-coding it into the formula; put this in B38:

Code: 
=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))
 
Upvote 0
strangebiscuit said:
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.
Click to expand...

OK, I missed the celing, adjusted to this...
=IF(A2>0,IF(A1<10,MIN(250,A2*42),MIN(350,150+40*(A2-1))),"")
 
Last edited:
Upvote 0
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.
 
Upvote 0
strangebiscuit said:
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 ...
Click to expand...

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. :)
 
Last edited:
Upvote 0
Hercules1946 said:
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. :)
Click to expand...

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,"")))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

imback2nite
  • Solved
VBA help.
2
Replies
10
Views
801
Peter_SSs
Peter_SSs
M
  • Question Question
Dynamic Data Validation
Replies
1
Views
55
Peter_SSs
Peter_SSs
E
  • Solved
Can someone tell me why the first column is not displaying all info?
Replies
3
Views
156
RoryA
RoryA
J
  • Solved
IF Statement with twelve conditions for time conditioning
Replies
2
Views
437
Jescanilla
J
D
  • Question Question
VBA to Export Tab to .CSV with date
Replies
1
Views
220
abdelfattah
A

Forum statistics

Threads
1,223,958
Messages
6,175,642
Members
452,663
Latest member
MEMEH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top