Plesae help: Excel function

S

Stu

Can someone please tell me why this function does not work:

SUM(IF(E199>"0",SUM(E199-D199)))

In case I have it all wrong, if I have a Value in cell E199 and I want to
subtract E199 from D199 provided that E199 has a value in it, if it does not
then I want Cell F199 where my sum is to remain blank.

Hope someone can help me with this.

Many thanks in advance

Stu
 
J

jchen

I tried it. It works. Maybe the cell is protected. Try testing it on a new
workbook. It works.

If you can't unprotect the cell, just type it manually, like
=SheetName!CellAddress, ie: =Sheet1!A1 for the 1st cell in Sheet1.
 
D

David Billigmeier

The logic you explained contradicts what you are showing in your formula, to
"subtract E199 FROM D199" would yield D199 - E199 (not the other way around).
Your logic is also a little confusing so I'll first re-state what I think
you mean and the provide a formula. If this is incorrect re-post a little
clearer.

Cell F199 has the following formula: If E199 has a value greater than 0
then display D199 - E199, otherwise display blank:

=IF(E199>0,D199-E199,"")
 
J

jchen

Aw, sorry, reply to the wrong place. Tried you question also. But the sum
function will always return a value, like 0. I only know the macro approach
here. Which will not be as dynamic unless it is triggered using cell change
event.

Basically it is like this
sub macro1(row as long)
if Trim(Cell(row, "E").FormulaL1C1) <> "" Then Cell(row, "A").FormulaL1C1
= Cell(row, "E").FormulaL1C1 - Cell(row, "D").FormulaL1C1
end sub

And put this macro in a worksheet change event.
 
Top