"IF" Function

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(D40formula10, formula10-D40, 0)



In cell D101 you put the following:

=IF(D40formula10, 0, D40-formula10)



In cell D12 you put the following:

=IF(D101formula12, formula12-D101,0)



In cell D102 you put the following:

=IF(D101formula12, 0, D101-formula12)



In cell D22 you put the following:

=IF(D102formula22, formula22-D102, 0)



In cell D103 you out the following:

=IF(D102formula22, 0, D102-formula22)



In cell D29 you put the following:

=IF(D102formula29, formula29-D102, 0)



In cell D104 you out the following:

=IF(D102formula22, 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(D40formula10, formula10-D40, 0)



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

I then placed the following formula in cell D101:

=IF(D40formula10, 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

Hello Lars, I tried to send you the spreadsheet but received the following
message:


wrote:
Sorry, I couldn't find any host named REMOOOVE.telia.com

If you would like me to send you the file, please send me a note

FORREGISTRY at YAHOO dot COM

and I reply with the spreadsheet.

Thank you again for ALL your help,
Bob