Formula Help

[Mike]

I have a spreadsheet that looks as follows:
A B
1 Net Income $4,000,000
2
3 Range Allocation
4 $2MM to $3MM $1,000,000
5 $3MM to $5MM $1,000,000
6 $5MM to $10MM 0

I am trying to write a formula that will allocate the net income to ecah
given range. For example, if the Net Income number were $1,500,000 there
would be no amounts in any range. If the Net Icome number were $2.5MM there
would be $500,000 in the $2-$3MM bucket and nothing in the other buckets. If
the net income number was $6,000,000 there would be $1,000,ooo in the first
bucket, $2,000,000 in the second bucket and $1,000,000 in the last bucket.

I would want separate formulas in Cells B4, B5 and B6. Thet need to be
flexible since the Net Income nuber will be an amount which can be
manipulated by thr user.

Thanks

 

[bpeltzer]

B4: =min(1000000,b1-2000000)
B5: =max(0,min(2000000,b1-3000000))
B6: =max(0,min(5000000,b1-5000000))
In each case, the first argument of MIN is the max range on this row and the
second argument is the net income minus the starting point for this row.
The last two add the max(0,...) to avoid a negative allocation if the net
income is below this row's starting point.

 

[Mike]

Thank you very much

B4: =min(1000000,b1-2000000)
B5: =max(0,min(2000000,b1-3000000))
B6: =max(0,min(5000000,b1-5000000))
In each case, the first argument of MIN is the max range on this row and the
second argument is the net income minus the starting point for this row.
The last two add the max(0,...) to avoid a negative allocation if the net
income is below this row's starting point.