Formula Help

  • Thread starter Thread starter Rebecca
  • Start date Start date
R

Rebecca

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.
 
Rebecca said:
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.
Click to expand...

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.
 
That worked but now because there is no information in the cells I get a
#Value! error...How do I get rid of this?
 
Rebecca said:
That worked but now because there is no information in
the cells I get a #Value! error...How do I get rid of this?
Click to expand...

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 -----
 
Back
Top