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]

[TABLE="width: 338"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]T1[/TD]
[TD]0[/TD]
[TD]299[/TD]
[TD]299[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]T2[/TD]
[TD]300[/TD]
[TD]349[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]T3[/TD]
[TD]350[/TD]
[TD]550[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]T4[/TD]
[TD]551[/TD]
[TD]650[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]T5[/TD]
[TD]651[/TD]
[TD]99999999[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

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!