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

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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

strangebiscuit

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,562
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

galt13

Board Regular
Joined
May 25, 2013
Messages
96
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723

ADVERTISEMENT

See if this will get you started (I used my own ranges, so change those as needed)...
A​
B​
1​
10​
2​
5​
310​
B2=IF(A1<10,A2*42,150+40*(A2-1)

You could keep adding to that with more IF's
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows
I see there are other answers, but I thought I'd construct a sample lookup table for you.
 
Last edited:

Hercules1946

Well-known Member
Joined
Oct 6, 2007
Messages
545
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

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

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,935
Members
413,953
Latest member
Arthur1471

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
Top