"IF" Function

Hi, I am using Excel MSOffice Xp (2002) and would appreciate help with the
"if" function. I am trying to create a Retirement formula and running into
all sorts of errors and will try and explain the scenario.
In the most simple context, I am basically trying to create a formula that
would subtracts EXPENSES from ASSETS on annual basis and adjust ASSETS to
account for the annual expenses.
Let me give you a scenario /example, and assume that Assets are $5,000,
Income is $100 and Expenses are $200.
I am trying to create a "if" formula that would be +100 (income) less 200
(expenses) and since this would be a negative number, I would go to assets
and subtract $100 so the formula would be "if" income - expenses is positive
the OK but if income - expenses is negative, subtract that amount from assets
and reduce assets accordingly. This would be annualized so in effect it
would be current year Income less Current year Expenses if this is a positive
number, it would be added to the following years assets. If this is negative,
then it is subtracted from current year Assets and the following years assets
would be reduced by this amount. The actual spreadsheet is obviously a
little more complicated, I several sources of income and several expense
categories with Inflation Factors but am stuck creating a "IF" formula. Any
help would be appreciated. Thanks, Bob

Hi Bob,

try
=IF(Income-Expense=0,"",Income-Expense+Asset)

and replace the words Income etc by the relevant cells

"Boblink" wrote:

Hi, I am using Excel MSOffice Xp (2002) and would appreciate help with the
"if" function. I am trying to create a Retirement formula and running into
all sorts of errors and will try and explain the scenario.
In the most simple context, I am basically trying to create a formula that
would subtracts EXPENSES from ASSETS on annual basis and adjust ASSETS to
account for the annual expenses.
Let me give you a scenario /example, and assume that Assets are $5,000,
Income is $100 and Expenses are $200.
I am trying to create a "if" formula that would be +100 (income) less 200
(expenses) and since this would be a negative number, I would go to assets
and subtract $100 so the formula would be "if" income - expenses is positive
the OK but if income - expenses is negative, subtract that amount from assets
and reduce assets accordingly. This would be annualized so in effect it
would be current year Income less Current year Expenses if this is a positive
number, it would be added to the following years assets. If this is negative,
then it is subtracted from current year Assets and the following years assets
would be reduced by this amount. The actual spreadsheet is obviously a
little more complicated, I several sources of income and several expense
categories with Inflation Factors but am stuck creating a "IF" formula. Any
help would be appreciated. Thanks, Bob

Thanks Chris,
=IF(Income-Expense=0,"",Income-Expense+Asset)
appears to work, thank you.
The formula calculates annual Networth, which is what I am trying to
determine but would really be great (and I am not sure if it can be done with
Excel) is to also SUBTRACT the amount that was deducted, from the Assets line?
To try and simply (rephrase) what I would like to do, the formula has taken
care of the Networth line which now "borrows" the shortfall from Assets and
calculates the remaining (current) Networth positions. which is great.
What it doesn't do is subtract the "shortfall" (that was taken from Assets),
from the Asset line i.e. reflecting the current string of Assets.
I believe that I could do this by staring a new row which will subtract
Current Networth from last years Assets and my question is, is it possible,
to incorporated this in the "IF' Formula (i.e. reduce assets accordingly)?
Thank you again for your help Jeff, I don't know what I was doing wrong but
everything that I tried came back with an error so thank you for pointing me
in the right direction.
Bob
Bob

Hi Chris, I realize that my follow-up question was not a very smart question
but I do have another question about the "IF" function. I have used the "IF"
function in the spreadsheet and later on, created another "IF" formula that
included the cell of the 1st "IF" formula and received an error message so I
am assuming that you can not refer to a previous "IF" cell with another "IF"
formula? Is this true or did I make an error in creating the formula "if"
formula and two "ifs" are doable?
Thanks,
Bob

IF() formulas can reference other cells with IF() formulas in them. For
instance:

A1: =IF(INT(RAND()*2), "One", "Zero")
A2: =IF(A1="Zero","One","Zero")


In article ,
Boblink wrote:

Hi Chris, I realize that my follow-up question was not a very smart question
but I do have another question about the "IF" function. I have used the "IF"
function in the spreadsheet and later on, created another "IF" formula that
included the cell of the 1st "IF" formula and received an error message so I
am assuming that you can not refer to a previous "IF" cell with another "IF"
formula? Is this true or did I make an error in creating the formula "if"
formula and two "ifs" are doable?
Thanks,
Bob

Hey thanks, the "IF and" was very helpful but like everything else, one
"problem" solved lead to another problem so I am back with another request
for help.

This formula:

=IF(AND(B39=0,B13-B440),B13,0)

did not do what I wanted it to do.
What I am trying to do is create a formula that says, if the value in B39 is
positive, use the value in B13 less B44, and if this value is positive, enter
B13 less B44, if it is negative, then enter "0".
What I am getting is a POSITIVE number when B13-B44 is zero (the result
should have been a negative number), what I would like is the cell is for it
to display "a zero" when a negative result is present.
Thank you in advance for your help,
Bob

On Sat, 17 May 2008 08:05:00 -0700, Boblink
wrote:

Hey thanks, the "IF and" was very helpful but like everything else, one
"problem" solved lead to another problem so I am back with another request
for help.

This formula:

=IF(AND(B39=0,B13-B440),B13,0)

did not do what I wanted it to do.
What I am trying to do is create a formula that says, if the value in B39 is
positive, use the value in B13 less B44, and if this value is positive, enter
B13 less B44, if it is negative, then enter "0".
What I am getting is a POSITIVE number when B13-B44 is zero (the result
should have been a negative number), what I would like is the cell is for it
to display "a zero" when a negative result is present.
Thank you in advance for your help,
Bob


You also have to specify what result you want if the value in B39
isn't positive.

Try this:

=IF(B390,IF(B13-B440, B13-B44, 0), 9999999)

replace 9999999 with the value you expect if B39 is not positive.

Hope this helps / Lars-Åke

Thank you for your help Lars, I appreciate you trying to straighten me out
since it appears that I need a LOT of help! Your advised me to "specify what
result you want if the value in B39 isn't positive" and I (slightly) modified
the formula as follows:

=IF(B390,IF(B13-B440,B13-B44,0),0)

but the results now show a POSITIVE value in cell B40 (i.e. the cell with
the formula) although cell B39 has a positive value.

I might have not stated "the problem" incorrectly so let me try rephrasing
it, what I am trying to do in cell B40 is calculate a "zero" when the value
in cell B39 is positive, calculate B13-B44 when the value in cell B39 is
"zero" and calculate a "zero" in cell B40 when the value of B13-B44 is
negative.

Thank you again for your help Lars,
Bob

On Sat, 17 May 2008 09:41:00 -0700, Boblink
wrote:

Thank you for your help Lars, I appreciate you trying to straighten me out
since it appears that I need a LOT of help! Your advised me to "specify what
result you want if the value in B39 isn't positive" and I (slightly) modified
the formula as follows:

=IF(B390,IF(B13-B440,B13-B44,0),0)

but the results now show a POSITIVE value in cell B40 (i.e. the cell with
the formula) although cell B39 has a positive value.

I might have not stated "the problem" incorrectly so let me try rephrasing
it, what I am trying to do in cell B40 is calculate a "zero" when the value
in cell B39 is positive, calculate B13-B44 when the value in cell B39 is
"zero" and calculate a "zero" in cell B40 when the value of B13-B44 is
negative.

Thank you again for your help Lars,
Bob


This time you have not specified what you want when B39 is negative.
Maybe that will never happen.

In that case, try the following:

=IF(B390,0,IF(B13-B440,B13-B44,0))

This table shows what happens in the 9 different cases:

B390 and B13B44 gives 0
B390 and B13=B44 gives 0
B390 and B12B44 gives 0
B39=0 and B13B44 gives 0
B39=0 and B13=B44 gives 0
B39=0 and B13B44 gives B13-B44
B390 and B13B44 gives 0
B390 and B13=B44 gives 0
B390 and B13B44 gives B13-B44

Is this what you want?
If not, provide a similar table covering ALL possible combinations
that should influence the result.

Lars-Åke

On Sat, 17 May 2008 17:08:25 GMT, Lars-Åke Aspelin
wrote:

On Sat, 17 May 2008 09:41:00 -0700, Boblink
wrote:

Thank you for your help Lars, I appreciate you trying to straighten me out
since it appears that I need a LOT of help! Your advised me to "specify what
result you want if the value in B39 isn't positive" and I (slightly) modified
the formula as follows:

=IF(B390,IF(B13-B440,B13-B44,0),0)

but the results now show a POSITIVE value in cell B40 (i.e. the cell with
the formula) although cell B39 has a positive value.

I might have not stated "the problem" incorrectly so let me try rephrasing
it, what I am trying to do in cell B40 is calculate a "zero" when the value
in cell B39 is positive, calculate B13-B44 when the value in cell B39 is
"zero" and calculate a "zero" in cell B40 when the value of B13-B44 is
negative.

Thank you again for your help Lars,
Bob


This time you have not specified what you want when B39 is negative.
Maybe that will never happen.

In that case, try the following:

=IF(B390,0,IF(B13-B440,B13-B44,0))

This table shows what happens in the 9 different cases:

B390 and B13B44 gives 0
B390 and B13=B44 gives 0
B390 and B12B44 gives 0
B39=0 and B13B44 gives 0
B39=0 and B13=B44 gives 0
B39=0 and B13B44 gives B13-B44
B390 and B13B44 gives 0
B390 and B13=B44 gives 0
B390 and B13B44 gives B13-B44

Is this what you want?
If not, provide a similar table covering ALL possible combinations
that should influence the result.

Lars-Åke

The B12 is a typo, should be B13 there as well.