"IF" Function

[Boblink]

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

 

[Chris]

Hi Bob,

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

and replace the words Income etc by the relevant cells

 

[Boblink]

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

 

[Boblink]

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

 

[JE McGimpsey]

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")

 

[Boblink]

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-B44>0),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

 

[Lars-Åke Aspelin]

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-B44>0),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(B39>0,IF(B13-B44>0, B13-B44, 0), 9999999)

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

Hope this helps / Lars-Åke

 

[Boblink]

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(B39>0,IF(B13-B44>0,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

 

[Lars-Åke Aspelin]

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(B39>0,IF(B13-B44>0,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(B39>0,0,IF(B13-B44>0,B13-B44,0))

This table shows what happens in the 9 different cases:

B39>0 and B13<B44 gives 0
B39>0 and B13=B44 gives 0
B39>0 and B12>B44 gives 0
B39=0 and B13<B44 gives 0
B39=0 and B13=B44 gives 0
B39=0 and B13>B44 gives B13-B44
B39<0 and B13<B44 gives 0
B39<0 and B13=B44 gives 0
B39<0 and B13>B44 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

 

[Lars-Åke Aspelin]

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(B39>0,0,IF(B13-B44>0,B13-B44,0))

This table shows what happens in the 9 different cases:

B39>0 and B13<B44 gives 0
B39>0 and B13=B44 gives 0
B39>0 and B12>B44 gives 0
B39=0 and B13<B44 gives 0
B39=0 and B13=B44 gives 0
B39=0 and B13>B44 gives B13-B44
B39<0 and B13<B44 gives 0
B39<0 and B13=B44 gives 0
B39<0 and B13>B44 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.

 

[Boblink]

"Thank you for your help guys, fyi I am trying to create a Retirement
Spreadsheet so I have replicated the formulas across columns "B" thru "AL"
(which represent years 2008-2044), and think that the problem is NOT with the
formula in row 40 but with a formula in ROW 13 (which is one of the cells
that the formula in row 40 references).

The cells in row 13 currently have a value of +99, and I tried this formula:

=IF(AND(B11>0,99-B44>0),99,B44-99)

but it does not come up with the results that I am looking for. What I want
is:

- if the value in cell B11 is positive, then the value +99 should appear in
cell B13

- if the value of cell B11 is negative, then the next calculation should be
+99 less the value in cell B44. If this result in a positive number, than
the POSITIVE value only should appear in cell B13

- if this result in a positive number, then that number should appear in
cell B13

- If the result is a negative number, then a "zero" should be appear in
cell B13

The formula that I have (=IF(AND(B11>0,99-B44>0),99,B44-99) displays +99 for
B13 when the value in cell B11 is positive (which is what I want), but it
displays a negative number when the value in B11 is "zero".

What I would like is, when the value in cell B11 is "zero", the value in
cell B13 should be +99-B44 but only until the value is POSITIVE, a positive
number ONLY should be displayed in B13.
Once the value in B11 is negative and the result of “+99 –B44†has become
negative, then "zero" should be displayed in cell B13.
Example:

- B11=125
- B44= 80
- C11=0
- C44=120
- D11=0
- D44=150

B13 - the calculations for cell B13 should be: "+99" , because there is
a positive value <+125> in cell B11

C13 - the calculations for cell C13 should be: "+99â€, because 99-120=
-21, therefore only a positive value, "+99" should be displayed / should
appear, in cell C13

D13 - the calculations for cell D13 should be: "zero", because the
results in C13 resulted in a negative number (i.e. the only number that
should appear is "+99" or "a POSITIVE number only when +99 is subtracted from
cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is
negative)

Dana, as you can probably tell, I am just familiarizing myself with the “IFâ€
function and have NOT used the “MAX†function. From your example, where you
suggested:

=MAX(B13-B44,0)
is to be used instead of
=IF(B13-B44>0,B13-B44,0)
It certainly would make sense to use the MAX function (i.e. smaller formula
= less chance for errors), if it gets the same results.
I have not located where I used:
=IF(B13-B44>0,B13-B44,0)
but being that this is a dynamic exercise, it might have been in a earlier
iteration nevertheless, when I tried to incorporate it into the correct
SpreadSheet, it did not result in the same calculations so I must have
misunderstood and / or, NOT followed what you suggested.


Lars, I assume that you are Swedish? What part of Sweden do you live in?
My wife grew up in Malma.

Thank you again for your help guys,
Bob

 

[Lars-Åke Aspelin]

Example:

- B11=125
- B44= 80
- C11=0
- C44=120
- D11=0
- D44=150

B13 - the calculations for cell B13 should be: "+99" , because there is
a positive value <+125> in cell B11

C13 - the calculations for cell C13 should be: "+99”, because 99-120=
-21, therefore only a positive value, "+99" should be displayed / should
appear, in cell C13

D13 - the calculations for cell D13 should be: "zero", because the
results in C13 resulted in a negative number (i.e. the only number that
should appear is "+99" or "a POSITIVE number only when +99 is subtracted from
cell 44 or "zero" when the calculation of "+99 less the value in cell 44" is
negative)

Please explain again the principle difference between the examples in
columns C and D.

Should the result in column D be dependent on the result in column C
as you write, or is that just a typo?

Lars-Åke

 

[Boblink]

Hello Lars, I apologize for the confusion and will try to explain the
scenarios a little better.

The calculations for B13 should result in +99 because there is a positive
value in cell B11 (125-80=45), so +99 is entered in cell B13. When the value
in cell 11 is positive, +99 (and only up to +99) should be entered as the
value for cell 13.

The calculation for C13 should also be +99 because in this case, the value
in cell B11 is -120 (0-120= -120), but cell B13 can not be negative and the
maximum allowable value is +99, so +99 is entered in cell B13. If the value
in cell 11 is positive, +99 should be entered as the value for cell 13. If
the value of cell 11 is <+99, than value in cell 11 is added to +99 and the
positive integer, up to +99 is entered in cell 13. If the value in cell 11
is >+99, then +99 is entered in cell 13 and for all subsequent years /
following years, the entry for cells 13 will be "zero". Once the +99 is
used, it is no longer available and "zero" will be the entry for cell 13.

The calculation for D13 cells should be "zero" because there is a negative
value in cell D11 and the " +99" has been already used the previous year, so
"zero" is the entry for cells 13 from this point forward.

I don't know if this makes sense to you Lars, if not, please advise and I
will try and send a different scenario / example.

I also have an unrelated question that you may or may not be able to help me
with Lars.
I am using a Vista system and when I tried to post a REPLY in the Newsgroup
form, nothing happened. My screen went blank (refreshes) for a second or so
and then the same page (with all the previous post) appeared, I was not
presented with the REPLY window.
I had to go to another (WindowsXp) system to post this note, I was unable
to get the Reply screen with the Vista system.
Have you heard of any other people with this problem of not being able to
access the REPLY screen? It certainly sounds like it is Vista related
problem, most likely with a security feature in Vista.
If you have any thoughts on this, I would appreciate hearing what you might
suggest to allow me to access the REPLY screen with the Vista system
Thank you again for your ALL help Lards,
Bob

 

[Lars-Åke Aspelin]

Sorry, but I am totally lost with your descriptions.
You keep changing the examples all the time. Or have a lot of typos.

A few lines below you say that the value in cell B11 is 45,
a bit further down you say that the value in cell B11 is -120.
And in a previous post, where you presented this example, you said
that the value in cell B11 is 125. Which of these are the correct one?

You write that C13 should be +99 for some strange reason involving B11
and B13 that I can't follow, but it ends up that also B13 should be
+99. A bit further down you write that once +99 has been entered in a
cell on row 13 all subsequent entries on row 13 should be "zero".
To me this is contradictory and makes no sense at all.

Please try to explain the problem more accurately and consistantly,
and it will increase the chances to get the help you ask for.

Try to describe, for one cell at a time, not all in the same sentence,
on which other cells this cells value will be dependent.

For example:
The value in a cell on row 11,
- will that be dependant on other cells in the same column (year)
if so, state which cells and which dependencies one at a time
- will that be dependant on any cells in any other column (year)
if so, state which cells and which dependencies one at a time

The value in a cell on row 13,
- will that be dependant on other cells in the same column (year)
if so, state which cells and which dependencies one at a time
- will that be dependant on any cells in any other column (year)
if so, state which cells and which dependencies one at a time

Lars-Åke

 

[Boblink]

Hi Lars, I apologize for being sloppy, I usually am not but I am trying to
describe “IF†Formulas, which is something that I am not very familiar with,
so I am confusing myself and obviously, confusing you as well.

Let me try this, I am enclosing a spreadsheet and will try and describe what
I am trying to do. As I mentioned when we started, this is a Retirement
calculation, which lists various sources of funds, adjusts them for inflation
and taxes, and then subtracts expenses, to determine how long “money†will
last.

So taking this into considerations:


Row 11 has an “IF†formula that adds the totals of rows 6 and 9, and
subtracts the amount in row 37.

The “IF†Formula for row 11 should be, if the value in row 11 (which is the
sum of row 6 and 9, less row 37) is positive, the positive number should be
entered in row 11. If the number is negative, “zero†should be entered in
row 11 and the negative value should be added to the next source of funds,
which will be row 13 (and “zero†should be entered in row 11 from that point
on).

Row 13 is one fixed number (+99 in this example), and the “IF†formula for
row 13 should be, if the sum of row 6 + row 9 – row 37, is positive, enter
the positive number in row 13. If the number is negative, then this negative
number should be brought down to / be added, to the next source of funds,
which is row 25 (and “zero†should be entered for line 13 from that point
on).

Row 25 is the sum of rows 21-23 adjusted for taxes and the “If “ Formula for
line 25 should be , if the results of row 6 + row 9 + row 13 less row 37 is
positive, row 25 should display the sums of row 21-23. If the result if the
results of row 6 + row 9 + row 13 less row 37 is negative, then negative
number should be added to the next source of funds, which is row 31 (and
“zero†should be entered for line 13 from that point on).


Row 31 is the sum of row 28-30 adjusted for taxes and the “IF†Formula for
line 31, should be, the sum of row 28-30 less 37 should be entered on line
31.

Note, there are no other sources of funds so if expenses exceed income /
assets, a negative number will appear on row 31, which will represent a
shortfall.





Column “A†“Bâ€
“C†“Dâ€
1
2
Taxable Retirement Accounts 3 2008 2009 2010
401K 4 $200 $123 $46
Cash (withdrawals) 5 $50 $50 $50
Withdrawls WITH Taxes (.35%) 6 $77 $77 $77
IRA 7 $100 $62 $23
Cash (withdrawals) 8 $25 $25 $25
Withdrawls WITH Taxes (.35%) 9 $38 $38 $38
10
Taxable Retirement Accounts 11 $185 $69 ($46)
12
ROTH IRA 13 $99 $99 $99
14
15
Year End Retirement - Totals 16 $284 $168 $53
17
18
19
Equity assets (flat) 20
stocks 21 $3,000 $3,000 $3,000
bonds 22 $1,000 $1,000 $1,000
Anuity 23 $500 $500 $500
Total Equity Assets 24 $4,500 $4,500 $4,500
Equities AFTER Taxes (.35%) 25 $2,925 $2,925 $2,925
26
27
Cash (.02%) 28 $1,000 $1,020 $1,040
Pensions 29 $75 $75 $75
Social Security (.01%) 30 $300 $303
Cash + Pensions + Social 31 $699 $907 $922
Security AFTER Tax 32
33
34
Available assets 35 $3,907 $3,600 $3,480
36
Expenses (.05%) 37 $400 $420 $441

 

[Boblink]

Once again I need to apologize, I see that the Spreadsheet that I enclosed
was formatted to fit the message screen and is all garbled. The spreadsheet
had 37 lines, many of them blanks (like lines / rows 1,2,10,12.........33,34
and 36).
I hope that the explainations that I included will give you a better idea of
what I am trying to achieve with this spreadsheet.
Thank you again for your help Lars,
Bob

 

[Boblink]

Just wanted to let you know, whatever was causing my Vista computer to not
allow me to access the REPLY screen, is no longer a problem. I was able to
POST the two previous notes with this Vista system. Whatever was causing
this problem appears to have "gone away".

 

[Boblink]

* Cells 11, 13, 25 and 31 contain positive numbers.
* Cells 11, 13 and 25 can NOT be negative, cell 31 CAN be negative
* Cell 37 should be subtracted from each of these cells individually in the
following sequence:
- The first calculation is, cell 37 subtracted from cell 11
- Once cell 11 is negative, cell 37 should be subtracted from cell 13
- Once cell 13 is negative, cell 37 should be subtracted from cell 25
- Once cell 25 is negative, cell 37 should be subtracted from cell 31
- If cell 31 is negative, cell 31 CAN contain a negative (if cell <37 less
cell 31> is negative, the negative number should appear in cell 31)
* Cells 11, 13 and 25 can not be negative; they can only contain a positive
number.
*If the results of the calculations are negative (that is cell 37 is greater
than the one of these cells), a “zero†should be entered into this cell and
the negative results should be brought forward to the next cell.

Example:
if B37 less B11 is positive, the positive number should appears in cell 11.
If C37 less C11 is negative, a “zero†should be entered for cell C11 and
the negative results be carried to the next cell in the sequence, C13.
The calculations for C13 would be (C37 less C13) less (C37 less C11).
The calculations for the “D†cells would be:
- Cell D11 is “zeroâ€
- D13 would be the results of (D37 less D13)
This continues until (D37 less D13) is a negative number
When D13 becomes negative, a “zero†should be entered in cell D13 and the
negative results brought to cell D25 (as was done when cell 11 became
negative)
Cells 11 and 13 would be “zero†and the calculations (cell 37 less cell 25)
continue until cell 25 is negative.
Once cell 25 becomes negative, the process is repeated for cell 31
If (cell 37- cell 31) results in a negative number, the NEGATIVE number
SHOULD be displayed in cell 31.
Cell 31 CAN display BOTH positive and negative numbers.

 

[Lars-Åke Aspelin]

* Cells 11, 13, 25 and 31 contain positive numbers.
* Cells 11, 13 and 25 can NOT be negative, cell 31 CAN be negative
* Cell 37 should be subtracted from each of these cells individually in the
following sequence:
- The first calculation is, cell 37 subtracted from cell 11
- Once cell 11 is negative, cell 37 should be subtracted from cell 13
- Once cell 13 is negative, cell 37 should be subtracted from cell 25
- Once cell 25 is negative, cell 37 should be subtracted from cell 31
- If cell 31 is negative, cell 31 CAN contain a negative (if cell <37 less
cell 31> is negative, the negative number should appear in cell 31)
* Cells 11, 13 and 25 can not be negative; they can only contain a positive
number.
*If the results of the calculations are negative (that is cell 37 is greater
than the one of these cells), a “zero” should be entered into this cell and
the negative results should be brought forward to the next cell.

Example:
if B37 less B11 is positive, the positive number should appears in cell 11.
If C37 less C11 is negative, a “zero” should be entered for cell C11 and
the negative results be carried to the next cell in the sequence, C13.
The calculations for C13 would be (C37 less C13) less (C37 less C11).
The calculations for the “D” cells would be:
- Cell D11 is “zero”
- D13 would be the results of (D37 less D13)
This continues until (D37 less D13) is a negative number
When D13 becomes negative, a “zero” should be entered in cell D13 and the
negative results brought to cell D25 (as was done when cell 11 became
negative)
Cells 11 and 13 would be “zero” and the calculations (cell 37 less cell 25)
continue until cell 25 is negative.
Once cell 25 becomes negative, the process is repeated for cell 31
If (cell 37- cell 31) results in a negative number, the NEGATIVE number
SHOULD be displayed in cell 31.
Cell 31 CAN display BOTH positive and negative numbers.

I try to rewrite this in other word. Is this still what you want?

First compare the values in cell 37 and cell 11.
If the value in cell 37 is less than or equal to the value in cell 11
then just subtract the value in cell 37 from the value in cell 11,
store the result in cell 11 and do nothing more to any cell.

Is this correct???

If the value in cell 37 is greater than the value in cell 11
then calculate the difference between the value in cell 37 and the
value in cell 11. Call this difference x. x is a positive number.
Store 0 in cell 11.
Now compare the value x to the value in cell 13.
If the value x is less than or equal to the value in cell 13
then just substract the value x from the value in cell 13, store the
result in cell 13 and do nothing more to any cell.

Is this correct??

If the value x is greater than the value in cell 13
then calculate the difference between the value x and the
value in cell 13. Call this difference y. y is a positive number.
Store 0 in cell 13.
Now compare the value y to the value in cell 25.
If the value y is less than or equal to the value in cell 25
then just subtract the value y from the value in cell 25, store the
result in cell 25 and do nothing moer to any cell.

Is this correct??

If the value y is greater than the value in cell 25
then calculate the difference between the value y and the
value in cell 25. Call this difference z. z is a positive number.
Store 0 in cell 25.
Finally subtract the value z from the value in cell 31 and store the
result in cell 31.

Is this correct??

Did you answer "YES" on all four questions above?

In that case the solution is near, because the problem is formulated.

To get shorter formulas it can sometimes be useful to make use of some
helper cells to store intermediate values, like x, y, and z above.

Assuming that we can use cell 101 for x, cell 102 for y and cell 103
for z, here is a number of formulas that you can try.

The formulas that you currently have in cell 11, cell 13, cell 25, and
cell 31 I call formula11, formula13, formula 25, and formula31
respectively.

In cell B11 you put the following:
=IF(B37<formula11, formula11-B37, 0)

In cell B101 you put the following:
=IF(B37<formula11, 0, B37-formula11)

In cell B13 you put the following:
=IF(B101<formula13, formula13-B101,0)

In cell B102 you put the following:
=IF(B101<formula13, 0, B101-formula13)

In cell B25 you put the following:
=IF(B102<formula25, formula25-B102, 0)

In cell B103 you out the following:
=IF(B102<formula25, 0, B102-formula25)

In cell B31 you finally put the following:
=formula31-B103


Exampel:
The original formulas in cells 11,13,25,31,and 37 have the values
11,13,25,31,and 90 respectively as their result.
After applying the above formulas the values will have changed to
0,0,0,-10,and 90 respectively.
And the helper cells 101,102,and 103 hold the values 79, 66, and 41
respectively.

When you have the results you expect with the aid of these helper
cells you can start to try to eliminate them.
The way ot doing this is to replace B101 wherever it occurs with the
formula in cell B101 (except the =) and the same for cells 102 and 103
But if you don't mind these helper cells you can just hide rows 101,
102, and 103.

Hope this helps.

Lars-Åke

 

[Boblink]

I try to rewrite this in other word. Is this still what you want?



First compare the values in cell 40 and cell 10.

If the value in cell 40 is less than or equal to the value in cell 10

then just subtract the value in cell 40 from the value in cell 10,

store the result in cell 10 and do nothing more to any cell.

Is this correct???

YES



If the value in cell 40 is greater than the value in cell 10

then calculate the difference between the value in cell 40 and the

value in cell 10. Call this difference x. x is a positive number.

Store 0 in cell 10.

Now compare the value x to the value in cell 12.

If the value x is less than or equal to the value in cell 12

then just subtract the value x from the value in cell 12, store
the result in cell 12 and do nothing more to any cell.

Is this correct??

YES



If the value x is greater than the value in cell 12

then calculate the difference between the value x and the
value in cell 12. Call this difference y. y is a positive number.

Store 0 in cell 12.

Now compare the value y to the value in cell 22.

If the value y is less than or equal to the value in cell 22

then just subtract the value y from the value in cell 22, store the

result in cell 22 and do nothing more to any cell.

Is this correct??

YES



If the value y is greater than the value in cell 22

then calculate the difference between the value y and the

value in cell 22. Call this difference z. z is a positive number.

Store 0 in cell 22.

Finally subtract the value z from the value in cell 35 and store the

result in cell 35.

Is this correct??

YES



Did you answer "YES" on all four questions above?

YES



But I made some modifications to the spreadsheet and changed the row numbers
(using Excel REPLACE function) as well as the start column which is now “Dâ€
instead of “Bâ€.

I believe the formulas that you provided (below) reflect these changes.

I also added other Source of Revenue, ROW 29, and believe that the formula
below is what should be used for ROW 29.





In that case the solution is near, because the problem is formulated.



To get shorter formulas it can sometimes be useful to make use of some
helper cells to store intermediate values, like x, y, and z above.



Assuming that we can use cell 101 for x, cell 102 for y and cell 103

for z, AND 104 for w, here is a number of formulas that you can try.



The formulas that you currently have in cell 10, cell 12, cell 22, and cell
35 I
call formula10, formula12, formula 22, and formula35 respectively.



In cell D10 you put the following:

=IF(D40<formula10, formula10-D40, 0)



In cell D101 you put the following:

=IF(D40<formula10, 0, D40-formula10)



In cell D12 you put the following:

=IF(D101<formula12, formula12-D101,0)



In cell D102 you put the following:

=IF(D101<formula12, 0, D101-formula12)



In cell D22 you put the following:

=IF(D102<formula22, formula22-D102, 0)



In cell D103 you out the following:

=IF(D102<formula22, 0, D102-formula22)



In cell D29 you put the following:

=IF(D102<formula29, formula29-D102, 0)



In cell D104 you out the following:

=IF(D102<formula22, 0, D102-formula29)



In cell D35 you finally put the following:

=formula35-D103





Example:

The original formulas in cells 10,12,22,35,and 40 have the values

10,12,22,35,and 90 respectively as their result.

I understand this (above) Lars, but everything from here are (below), I do
NOT follow:





After applying the above formulas the values will have changed to

0,0,0,-10,and 90 respectively.

And the helper cells 101,102,and 103 AND 104 hold the values 79, 66, and
41 respectively.

When you have the results you expect with the aid of these helper cells you
can start to try to eliminate them.

The way to doing this is to replace D101 wherever it occurs with the

formula in cell D101 (except the =) and the same for cells 102 and 103

But if you don't mind these helper cells you can just hide rows 101,

102, and 103.



Hope this helps.

Thank you Lars, not only does it help me create the spreadsheet but more
importantly, it provides me with education / helps me understand the use of
these functions, so hopefully in the future, I will not require SO MUCH help.
Bob


As far as the results, as soon as I placed the first formula



=IF(D40<formula10, formula10-D40, 0)



in cell D10, cell D-10 displayed #NAME?

I then placed the following formula in cell D101:

=IF(D40<formula10, 0, D40-formula10)

and #NAME? is what was displayed for D40.



I copied the appropriate formulas for D12, D22, D29 and D35 (as well as
D102, D103 and D104), and #NAME? is what was displayed on ALL these cells.



I understand that these formulas are VERY TRICKY (sensitive), if you have a
comma or a parenthesis or a number, in the WRONG position, the formula will
NOT work so I question, is this caused by Operator Error (i.e. sloppiness on
my part) or does the formula need to be modified?



If it helps Lars, I will be glad to POST the spreadsheet or send it to
you?



Thank you again for everything that you have done,

Bob