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!

 

[strangebiscuit]

Pardon again, perhaps I shouldn't have used the CODE tags for that.

The horrible current chain of IF statements is:

=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))))))

 

[Smitty]

FYI - I deleted your duplicate thread.

 

[Dave Patton]

I did not consider all your criteria but you do not need another sheet to use Lookup.
You could use Defined Name and Lookup

=LOOKUP(B36,Att_A)

Att_A defined as
={1,150;2,190;3,230;4,270;5,310;6,350}

 

[Michael M]

I think you've answered your own question....you either need a Lookup table
But why can't you use VBA ???

 

[galt13]

I can seem to see the pattern to your tiers. I don't even know what your 3rd, 4th and 5th tiers are comprised of, just what their top value is so I can't build you a formula to do it. That said it looks like it should be doable with a sumproduct formula but you need to provide better information.

 

[FDibbins]

See if this will get you started (I used my own ranges, so change those as needed)...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

10​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]

5​

[/td][td]

310​

[/td][/tr]
[/table]
B2=IF(A1<10,A2*42,150+40*(A2-1)

You could keep adding to that with more IF's

 

[DRSteele]

I see there are other answers, but I thought I'd construct a sample lookup table for you.

 

[Hercules1946]

Try This:
=IF(B36<10, 42*B37,IF(B36<=14, ((B37-1)*40)+150,IF(AND(B36<=19,B37=6),400,IF(AND(B36<=24,B37=6),450,IF(AND(B36>=25,B37=6),500,"")))))

Is it correct that if B36 is more than 14, you only want a value to be returned if B37 is 6? (Thats what you say in the rules)

 

[strangebiscuit]

I can seem to see the pattern to your tiers. I don't even know what your 3rd, 4th and 5th tiers are comprised of, just what their top value is so I can't build you a formula to do it. That said it looks like it should be doable with a sumproduct formula but you need to provide better information.

Sorry, I know it's complicated, thanks everyone for even taking a look.

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 from the maximum total for that tier. So for tier 3 ("points between 15 and 19") with the maximum attendance of 6, the total is 400. If attendance is 5, the total in B38 should be 360 (400 - 40). If attendance is 4, the total in B38 should be 320 (400 - 80). If attendance is 3, total in B38 should be 280 (400 - 120). And so on...