negative positive formulas

[Guest]

Hi, I need help figuring out how to program a formula.

I will have one cell displaying the monthly budget, another cell displaying
the actual amount spent.

now, when subtracted, i'd like to have one cell that only displays the value
if we are under budget, and another cell that only displays the value if we
are over budget. is there a formula to do this? or visual basic?

please let me know if you can help!

thanks

Em )

 

[Gordon]

Hi, I need help figuring out how to program a formula.

I will have one cell displaying the monthly budget, another cell
displaying the actual amount spent.

now, when subtracted, i'd like to have one cell that only displays
the value if we are under budget, and another cell that only displays
the value if we are over budget. is there a formula to do this? or
visual basic?

please let me know if you can help!

thanks

Em )

Say A1 is the budget and B1 is the expenditure
In C1 enter =IF(A1>B1,A1-B1,"") and in D1 enter =IF(B1>A1,A1-B1,"")
C1 will show the value if under budget, and D1 if over budget.

HTH

 

[JE McGimpsey]

one way:

A2: <budget>
B2: <actual>
C2: =IF(B2<=A2, B2, "")
D2: =IF(B2<A2,"", B2)

Where C2 will display the actual value if you're under (or on) budget
and D2 will display the actual value if you're over budget.

 

[Steve H.]

If your data is in A1 and B1 then in B3 use this =IF(A1-B1>0,A1-B1,"") in B4
use this =IF(B1-A1<0,B1-A1,"").

Steve

 

[crispbd]

Let's say the final 'Actual Spent' amount, (positive or negative) wind
up in column C.

In Column D we display the value if <= Budget:
=If(C1>=0,C1,"")

In Column E we can display the value if over budget (where C < 0)

=If(C1<0,C1,"")

Of course, instead of the actual spent amount being in column c you ca
include its calculation in the above formulas

 

[Steve H.]

Sorry I meant in C1 use this =IF(A1-B1>0,A1-B1,"") and in D1 use this
=IF(A1-B1<0,A1-B1,"")

Steve

 

[Guest]

Emmy,

This could easily be done with a couple "if" statements. Let's say that
your budget amount is in cell A1 and your actual amount spent in B1 and then
underbudget cell is C1 and overbudget cell is D1. Then in C1 (for
underbudget) you would put an if statement that says: =IF(A1-B1>=0,A1-B1,"")
which says that if the budget amount minus the amount spent is greater than
or equal to zero then show that amount, otherwise, show nothing. In cell D1
you would put a similar formula that says: =IF(A1-B1<0,A1-B1,""). Now this
will show a nebative number in the cell if you are overbudget. If you don't
want that (i.e. to show overbudget as a pos #) then modify the equation to
say: =IF(A1-B1<0,B1-A1,""). One last thing, if you are trying to add up the
columns later, sometimes the "" part might give you problems depending on
what formlas you use (probably not, but just in case) if so, just replace the
"" with zero and then you could do some conditional formtting on the cells to
hide the zero if you don't want it to show.

 

[Guest]

Thanks, that worked perfectly!!

Em )

Say A1 is the budget and B1 is the expenditure
In C1 enter =IF(A1>B1,A1-B1,"") and in D1 enter =IF(B1>A1,A1-B1,"")
C1 will show the value if under budget, and D1 if over budget.

HTH