Logical Formulas

[debinnyc]

I am trying to build a formula that if values change, the formula changes.
For example, if there is a small column of numbers such as line budgets. If
those budgets are revised, the new budget would be placed in the next column,
with a max of two revisions. I have an actual YTD costs, and a balance
formula. What I need is for the balance formula to calculate against the
budgeted amount (i.e. =budget-YTD). So if the budget is revised, I want the
balance to automatically consider the revised amount, not the original
amount.
I have tried using the IF stmt, but I cannot get the formula to consider the
revisions. Can anyone please help? Thanks

 

[DaveO]

Hi, Deb-
Let's say the budget column is A, the first revision column is B, the
second revision column is C; actuals YTD are in D, and the Balance
formula is in E. Let's assume the revisions are blank until they are
updated, since it's possible for a budget to be revised to zero.
(Trying to avoid a logic flaw, here.)

Try this formula:
=IF(C4<>"",C4-D4,IF(B4<>"",B4-D4,A4-D4))

It works from right to left to find the first non-blank entry starting
with revision 2; if revision 2 is blank it checks for a revision 1; if
rev 1 is blank it performs the math on the original budget amount.

Will that get you where you need to go?

Dave O

 

[debinnyc]

Still not working. I copied the formula exactly to the spreadsheet, and
filled in the appropriate cell locations. The formula does not have any
errors, but the result of the formula shows a negative value of the current
YTD, in other words 0-YTD, not the budgeted as desired. For reference if it
helps, the columns are:

Budget C74
Rev 1 E74
Rev 2 G74
YTD I74

Thanks

 

[debinnyc]

Is there anyone that can help? Please advise.

Still not working. I copied the formula exactly to the spreadsheet, and
filled in the appropriate cell locations. The formula does not have any
errors, but the result of the formula shows a negative value of the current
YTD, in other words 0-YTD, not the budgeted as desired. For reference if it
helps, the columns are:

Budget C74
Rev 1 E74
Rev 2 G74
YTD I74

Thanks