Calculating Tiers

[rjblair]

I'm trying to calculate amounts of units in tiers. So the tiers are:

T1 0-299
T2 300-349
T3 350-550
T4 551-650
T5 651 -

so if I put in 250, it should show 250 in the first tier. If I entered 600, it would show 299 in first tier, 50 in second tier, 201 in the 3rd and 50 in the 4th (for a total of 600) and so on. I've tried using "if" statements but can't get past the second tier. I'm using this to calculate commissions and can easily do this once I have the amount of "units" in each tier.

Thanks!!!!

RJ

 

[RonB1111]

	A	B	C	D
1				600
2
3	T1	0	299	299
4	T2	300	349	50
5	T3	350	550	201
6	T4	551	650	50
7	T5	651	99999999	0

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

Using the above layout, enter your amount in D1. Enter this formula in D3 and copy down:

=MIN(C3-C2,D$1-SUM(D$2:D2))

If this is not what you're looking for, please show a sample of how you'd like the results to look.

 

[rjblair]

RON!!!!! I've been fumbling around like an idiot with these long formulas and you come in with this?!!! BOOM, it works!!! awesome! THANK YOU!!!!!

 

[RonB1111]

You're welcome!