Prev: Average IFS (Multiple Criteria)
Next: Find duplicate emails within a single Excel column
From: Rebecca on 28 Apr 2010 14:50
I need a formula that will take A1 x B1 but if the number is less then 0 it
will only how up as 0 and if its more then 0 if will show up as the real
number.
From: Joe User on 28 Apr 2010 15:09
"Rebecca" wrote:
> I need a formula that will take A1 x B1 but if the
> number is less then 0 it will only how up as 0 and
> if its more then 0 if will show up as the real number.

Try:

=max(0, A1*B1)

PS: For the future, it would be wise to choose a more distinctive subject;
e.g. "Multiply But No Less Than Zero?". By choosing such a non-descript
title, your posting might get lost as a response to other similarly-named
threads in some "news" (aka discussion group) readers.
From: Rebecca on 28 Apr 2010 15:53
That worked but now because there is no information in the cells I get a
#Value! error...How do I get rid of this?

"Joe User" wrote:

> "Rebecca" wrote:
> > I need a formula that will take A1 x B1 but if the
> > number is less then 0 it will only how up as 0 and
> > if its more then 0 if will show up as the real number.
>
> Try:
>
> =max(0, A1*B1)
>
> PS: For the future, it would be wise to choose a more distinctive subject;
> e.g. "Multiply But No Less Than Zero?". By choosing such a non-descript
> title, your posting might get lost as a response to other similarly-named
> threads in some "news" (aka discussion group) readers.
From: Joe User on 28 Apr 2010 16:15
"Rebecca" wrote:
> That worked but now because there is no information in
> the cells I get a #Value! error...How do I get rid of this?

Take you pick, depending on what you want....

The following requires values in both cells:

=if(count(A1,B1)=2, max(0, A1*B1), "")

The following requires a value in at least one cell:

=if(count(A1,B1), max(0, N(A1)*N(B1)), "")

The following returns zero if there are no values:

=max(0, N(A1)*N(B1))

Caveat: Note that N(A1) returns zero even if A1 is the __text__, not
number, "123". However, if A1 contains text that appears to be a number,
A1*B1 will properly interpret the number. For example, if B1 is 2, A1*B1 is
the __number__ 246. If this behavior of N(A1) is problemmatic, post a
follow-up for alternative solutions, if the first one above does not satisfy
your needs.


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

"Rebecca" wrote:

> That worked but now because there is no information in the cells I get a
> #Value! error...How do I get rid of this?
>
> "Joe User" wrote:
>
> > "Rebecca" wrote:
> > > I need a formula that will take A1 x B1 but if the
> > > number is less then 0 it will only how up as 0 and
> > > if its more then 0 if will show up as the real number.
> >
> > Try:
> >
> > =max(0, A1*B1)
> >
> > PS: For the future, it would be wise to choose a more distinctive subject;
> > e.g. "Multiply But No Less Than Zero?". By choosing such a non-descript
> > title, your posting might get lost as a response to other similarly-named
> > threads in some "news" (aka discussion group) readers.
 | 
Pages: 1
Prev: Average IFS (Multiple Criteria)
Next: Find duplicate emails within a single Excel column