#Value

[Robbyn]

=IF(OR(D2>0, E2>0),(Lists!E2+SUM(D2))-SUM(E2),"")

How do I avoid the #value error when Lists!e2 is blank?

[mangesh_yadav]

Use:
=if(ISBLANK(Lists!E2),0,IF(OR(D2>0, E2>0),(Lists!E2+SUM(D2))-SUM(E2),""))
This places a 0 when Lists!E2 is blank. If you want a blank instead of 0, the use "" instead of 0 in the above formula.

- Mangesh

[Max]

Try this revised formula instead:

=IF(OR(D2>0, E2>0),SUM(Lists!E2,D2)-E2,"")

SUM(..) will ignore text

Think it was likely to be a stray space entry (made with spacebar) in
Lists!E2 which caused the error, rather than the cell being blank
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Robbyn" <[email protected]> wrote in message
news:[email protected]...
> =IF(OR(D2>0, E2>0),(Lists!E2+SUM(D2))-SUM(E2),"")
>
> How do I avoid the #value error when Lists!e2 is blank?

[Robbyn]

Thanks much Max!

"Max" wrote:

> Try this revised formula instead:
>
> =IF(OR(D2>0, E2>0),SUM(Lists!E2,D2)-E2,"")
>
> SUM(..) will ignore text
>
> Think it was likely to be a stray space entry (made with spacebar) in
> Lists!E2 which caused the error, rather than the cell being blank
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8, 1° 22' N 103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "Robbyn" <[email protected]> wrote in message
> news:[email protected]...
> > =IF(OR(D2>0, E2>0),(Lists!E2+SUM(D2))-SUM(E2),"")
> >
> > How do I avoid the #value error when Lists!e2 is blank?
>
>
>

[Max]

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Robbyn" <[email protected]> wrote in message
news:[email protected]...
> Thanks much Max!