How To Set a MAX Value, But when Exceeded Turns Negative

Hi Excel Forum Team,

I’m creating a new cash flow spreadsheet for my project controls team members to use and I am having trouble creating the formula described below and contained in the attachment/link below.

I need a formula for the Budget Utilized column that will the compare the Approved Budget column against the Forecast column and if there is enough of the Approved Budget ($1,000,000) for the project manager to use or burn in their Forecast for the initial scheduled coming months ahead (May-14 to Aug-14), for every month the Budget Utilized column (Example: Aug-14 $300,000) will equal the Forecast (Example: Aug-14 $300,000). So, basically saying that yes up to the month of Aug-14 there is enough Budget to pay for the amount you wish to Forecast.

However, if there is not enough of the Approved Budget ($1,000,000) for the project manager to use or burn in their Forecast for the extended new scheduled months ahead (May-14 to Dec-14), after the Budget Utilized column formula now shows a cumulative negative value (Example: Dec-14 -$225,000) that they have now exceeded the amount they had approved to spend. So, basically saying that no, there is not enough Budget to pay for the amount the you need to spend in your new scheduled Forecast and you will need an additional amount of $225,000 to supplement the rest of your project.

I also need to have the Budget Utilized formula extend out for a couple of months, comparing itself with the previous month’s Forecast and so on until the last month the year to date Forecast total finally exceeds the Approved Budget by showing a negative value in the Budget Utilized column.

Thank you for all your help and let me know if I can provide any additional information.

Max Value Example.xlsx

Garrett

Hi Gak67,

That's exactly the formula I needed, you did it!!!

Thank you for all your help! :-)

Garrett