Thread: IF/OR Assistance with Progressive Splits

Marcol, A couple of weeks ago you helped me tremendously with a nightmare IF/AND statement and turned into something manageable. There's one more piece I can't figure out for the life of me.

Here is the statement you created.

=IF(E9="","",
IF(OR(E9="Cookie",E9="Popcorn"),IF(G9<2000,5,IF(G9<3200,4.5,4)),
IF(OR(E9="TKT",E9="GC20"),IF(G9<501,10,IF(G9<1001,8,IF(G9<1501,7,IF(G9<2001,6,5)))),
INDEX(Table,MATCH($E9,Alias,0),3)))*$G9)

It works beautifully. However, there is one piece that's not taken into consideration. The TKT and GC20 shares are progressive values. Right now it's programmed as follows:

- 1-500 units = $10
- 501-1000 = $8
- 1001-1500 = $7
- 1501-2000 = $6
- 2001+ = $5

The problem is that the first 500 units will always be valued at $10 and units 501-1000 are valued at $8. Units 1001-1500 are valued at $7, etc.

Here's how I see it:

- At 501 units, the value is *8+1000
- At 1001 units, the value is *7+1500
- At 1501 units, the value is *6+2000
- At 2001 units, the value is *5+2500

I've tried all sorts of variations to calculate it correctly but I keep getting the error of "too many functions" or it comes back with a value in the millions! Can you help me make sense of this?

This could be
In Sheet "HS & Community" H9

=IF(E9="","",
IF(OR(E9="TKT",E9="GC20"),
IF(G9<501,10*G9,IF(G9<1001,(10*500)+(8*(G9-500)),IF(G9<1501,(10*500)+(8*500)+(7*(G9-1000)),IF(G9<2001,(10*500)+(8*500)+(7*500)+(6*(G9-1500)),(10*500)+(8*500)+(7*500)+(6*500)+(5*(G9-2000)))))),
IF(E9="","",IF(OR(E9="Cookie",E9="Popcorn"),
IF(G9<2000,5,IF(G9<3200,4.5,4)),
IF(OR(E9="TKT",E9="GC20"),
IF(G9<501,10,IF(G9<1001,8,IF(G9<1501,7,IF(G9<2001,6,5)))),
INDEX(Table,MATCH($E9,Alias,0),3)))
*$G9)))

This could be "simplified" to

=IF(E9="","",
IF(OR(E9="TKT",E9="GC20"),
IF(G9<501,10*G9,IF(G9<1001,5000+(8*(G9-500)),IF(G9<1501,9000+(7*(G9-1000)),IF(G9<2001,12500+(6*(G9-1500)),15500+(5*(G9-2000)))))),
IF(OR(E9="Cookie",E9="Popcorn"),
IF(G9<2000,5,IF(G9<3200,4.5,4)),
IF(OR(E9="TKT",E9="GC20"),
IF(G9<501,10,IF(G9<1001,8,IF(G9<1501,7,IF(G9<2001,6,5)))),
INDEX(Table,MATCH($E9,Alias,0),3)))
*$G9))

Maintenance for either could well be a problem.

See if this works for you, I don't know if there are different nesting limits with 2008, I think it's the same as 2007.

I suspect that you might have a similar problem with "Cookie and "Popcorn", if so we can look at a different tack for this problem.

This "solution" is pretty messy as it stands, and I would look at it in a different way should it need further expansion.

Hope this helps.