I'd like a formula that examines a cell and an array to determine what, if any funds are available to fund a reference cell.
Example:
What would be the best formula to calculate the Available column?
The formula should check the budget against the requested line item amount, but also needs to account for all the previous items, as applicable.
This is my first approach (starting in C5):
=if(sum($B$4:$B5)>$B$1,max($B$1-sum($B$4:$B4),0),min($B$1-sum($B$3:$B4),$B$1))
The weird/awkward part of my formula is I had to include the cell B3, which is not technically part of the table. It's the header. Otherwise, the formula in C4 doesn't work.
My attempt is probably cluncky-er than it needs to be and one of you excel pros will have a better solution to do this.
Example:
| Budget | $500 | |
| Request: | Available: | |
| Item (Priority #1) | $350 | $350 |
| Item (Priority #2) | $100 | $100 |
| Item (Priority #3) | $250 | $50 |
| Item (Priority #4) | $75 | $0 |
What would be the best formula to calculate the Available column?
The formula should check the budget against the requested line item amount, but also needs to account for all the previous items, as applicable.
This is my first approach (starting in C5):
=if(sum($B$4:$B5)>$B$1,max($B$1-sum($B$4:$B4),0),min($B$1-sum($B$3:$B4),$B$1))
The weird/awkward part of my formula is I had to include the cell B3, which is not technically part of the table. It's the header. Otherwise, the formula in C4 doesn't work.
My attempt is probably cluncky-er than it needs to be and one of you excel pros will have a better solution to do this.
