IF Statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

phuser - 16 Jun 2006 22:48 GMT

=IF(SUM(G7-M7)>0,SUM(G7-M7)," ")

I love how this keeps the cells clean BUT because there is no value in the
cell, I cant calculate any further,
all I get in return is a #Value
Is it possible to use 2 or more cells like this in a calulation? and if so
what syntax would I use?

Reply to this Message

Toppers - 16 Jun 2006 23:39 GMT

Can you explain why you can't calculate further (cecause of the ""?) and what
exactly you want to do?

> =IF(SUM(G7-M7)>0,SUM(G7-M7)," ")
>
[quoted text clipped - 3 lines]
> Is it possible to use 2 or more cells like this in a calulation? and if so
> what syntax would I use?

Reply to this Message

MyVeryOwnSelf - 17 Jun 2006 00:05 GMT

> =IF(SUM(G7-M7)>0,SUM(G7-M7)," ")
>
[quoted text clipped - 3 lines]
> Is it possible to use 2 or more cells like this in a calculation? and
> if so what syntax would I use?

One way is to use
Tools > Options View
and uncheck "zero values." This means, "if a value is zero, display it as
empty." Then you can use the simple formula like
=MAX(G7-M7,0)
and do further arithmetic on the result, while keeping the cells looking
clean.

A different way is to use the "N" function.

Suppose A1 contains: =IF(G7>M7,G7-M7," ")
and A2 contains: =IF(H7>N7,H7-N7," ")
(your formula but a bit shorter).

Then a formula such as
=N(A1)+N(A2)
Treats A1 or A2 as zero in the troublesome cases.

Excel's built-in Help explains what "N" does.

Reply to this Message