Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
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" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Help
Thank you very much
"bpeltzer" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |