#### strangebiscuit

##### New Member

- Joined
- Nov 25, 2013

- Messages
- 35

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**

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!