software

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

quot;Mikequot; wrote:

gt; I have a spreadsheet that looks as follows:
gt; A B
gt; 1 Net Income $4,000,000
gt; 2
gt; 3 Range Allocation
gt; 4 $2MM to $3MM $1,000,000
gt; 5 $3MM to $5MM $1,000,000
gt; 6 $5MM to $10MM 0
gt;
gt; I am trying to write a formula that will allocate the net income to ecah
gt; given range. For example, if the Net Income number were $1,500,000 there
gt; would be no amounts in any range. If the Net Icome number were $2.5MM there
gt; would be $500,000 in the $2-$3MM bucket and nothing in the other buckets. If
gt; the net income number was $6,000,000 there would be $1,000,ooo in the first
gt; bucket, $2,000,000 in the second bucket and $1,000,000 in the last bucket.
gt;
gt; I would want separate formulas in Cells B4, B5 and B6. Thet need to be
gt; flexible since the Net Income nuber will be an amount which can be
gt; manipulated by thr user.
gt;
gt; Thanks
gt;
gt;

Thank you very much

quot;bpeltzerquot; wrote:

gt; B4: =min(1000000,b1-2000000)
gt; B5: =max(0,min(2000000,b1-3000000))
gt; B6: =max(0,min(5000000,b1-5000000))
gt; In each case, the first argument of MIN is the max range on this row and the
gt; second argument is the net income minus the starting point for this row.
gt; The last two add the max(0,...) to avoid a negative allocation if the net
gt; income is below this row's starting point.
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I have a spreadsheet that looks as follows:
gt; gt; A B
gt; gt; 1 Net Income $4,000,000
gt; gt; 2
gt; gt; 3 Range Allocation
gt; gt; 4 $2MM to $3MM $1,000,000
gt; gt; 5 $3MM to $5MM $1,000,000
gt; gt; 6 $5MM to $10MM 0
gt; gt;
gt; gt; I am trying to write a formula that will allocate the net income to ecah
gt; gt; given range. For example, if the Net Income number were $1,500,000 there
gt; gt; would be no amounts in any range. If the Net Icome number were $2.5MM there
gt; gt; would be $500,000 in the $2-$3MM bucket and nothing in the other buckets. If
gt; gt; the net income number was $6,000,000 there would be $1,000,ooo in the first
gt; gt; bucket, $2,000,000 in the second bucket and $1,000,000 in the last bucket.
gt; gt;
gt; gt; I would want separate formulas in Cells B4, B5 and B6. Thet need to be
gt; gt; flexible since the Net Income nuber will be an amount which can be
gt; gt; manipulated by thr user.
gt; gt;
gt; gt; Thanks
gt; gt;
gt; gt;