Plesae help: Excel function

[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

[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.

"Stu" wrote:

> 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

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

--
Regards,
Dave


"Stu" wrote:

> 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

[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.


"jchen" wrote:

> 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.
>
> "Stu" wrote:
>
> > 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

[jchen]

Hi, try this.
=IF(E199="", "", E199-D199)

"Stu" wrote:

> 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

[PCLIVE]

Try this:

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

"Stu" <Stu@discussions.microsoft.com> wrote in message
news:7C33072F-9BC8-408B-ACE4-D915A0D13986@microsoft.com...
> 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