Prev: formula required
Next: combining dates and text in a cell
From: Danny Boy on 19 Mar 2010 13:23
Trying to resolve this formula to work appropritely in cell E7:

=SUM(E2:E6),IF(E2:E6=0,"No Balance Owed")

In other words, all currency outcomes are totaled in cell E7, however, if
all outcomes in cells E2:E6 are $0.00, than I'd like cell E7 to say "No
Balance Owed".

Any help would be appreciated!
Thanks
From: Joe User on 19 Mar 2010 14:14
"Danny Boy" wrote:
> In other words, all currency outcomes are totaled
> in cell E7, however, if all outcomes in cells E2:E6
> are $0.00, than I'd like cell E7 to say "No
> Balance Owed".

Try:

=if(round(sum(E2:E6),2)=0, "No Balance Owed", sum(E2:E6))

To understand the need for ROUND, at least in the first argument, try the
following experiment: =10.1-10-0.1 formatted as General. It's an anomaly of
computer arithmetic that we all need to be aware of. It would be prudent to
use ROUND even in the third argument.

Alternatively, do:

=round(sum(E2:E6),2)

with the Custom format 0.00;-0.00;"No Balance Owed".


----- original message -----

"Danny Boy" wrote:
> Trying to resolve this formula to work appropritely in cell E7:
>
> =SUM(E2:E6),IF(E2:E6=0,"No Balance Owed")
>
> In other words, all currency outcomes are totaled in cell E7, however, if
> all outcomes in cells E2:E6 are $0.00, than I'd like cell E7 to say "No
> Balance Owed".
>
> Any help would be appreciated!
> Thanks
From: Danny Boy on 19 Mar 2010 16:31
Thanks for the help and the information Joe!

Best,

Dan

"Joe User" wrote:

> "Danny Boy" wrote:
> > In other words, all currency outcomes are totaled
> > in cell E7, however, if all outcomes in cells E2:E6
> > are $0.00, than I'd like cell E7 to say "No
> > Balance Owed".
>
> Try:
>
> =if(round(sum(E2:E6),2)=0, "No Balance Owed", sum(E2:E6))
>
> To understand the need for ROUND, at least in the first argument, try the
> following experiment: =10.1-10-0.1 formatted as General. It's an anomaly of
> computer arithmetic that we all need to be aware of. It would be prudent to
> use ROUND even in the third argument.
>
> Alternatively, do:
>
> =round(sum(E2:E6),2)
>
> with the Custom format 0.00;-0.00;"No Balance Owed".
>
>
> ----- original message -----
>
> "Danny Boy" wrote:
> > Trying to resolve this formula to work appropritely in cell E7:
> >
> > =SUM(E2:E6),IF(E2:E6=0,"No Balance Owed")
> >
> > In other words, all currency outcomes are totaled in cell E7, however, if
> > all outcomes in cells E2:E6 are $0.00, than I'd like cell E7 to say "No
> > Balance Owed".
> >
> > Any help would be appreciated!
> > Thanks
 | 
Pages: 1
Prev: formula required
Next: combining dates and text in a cell