Value of Less Than Zero to Equal Zero

[thomasstyron]

Hello:

I am trying to format my answers in cells of a worksheet.

I have an equation that uses addition, subtraction, multiplication and
division. This equation has dependent variables in other cells and when
these cells are filled in with data, the original equation yields a
number. Unfortunately, if one of the cells is not used, there is still
a value reported by the original equation.

As an example: If B2=((A2*3)+(A3)-1.5) and A2=2 and A3=1, then the
result will be equal to 5.5. But if cell A2 does not have a value, the
value will report -0.5. Here is the problem. Well, I think I have two
problems.

The first is, how do I make my function compute -only after - all of
the values are entered?

And second, and maybe harder, is how do I make any values of a number
display zero if the result is less than zero? I tried using the IF
Logic function and using the following:
Logic Test: B2<0 (where B2 is the cell with the equation)
Value if true: 0
Value if False: B2 (I wanted the actual value to be reported if the
number was greater than zero)

This attempt was hit or miss at best. If the value was less than zero,
it would report zero; this is what I wanted. But if it was not less
than zero, then it would still report zero. Bummer.

If this second problem could be fixed, then the next thing that I would
ask is how do I apply that same IF Logic function to other cells? (as if
I set the conditions of the IF Logic function to one cell in a column
and I wished to apply them to subsequent cells in the same column).

I hope this isn’t too confusing.

Thank you in advance for any help that can be offered.

Best regards to all,
Thomas Styron
(e-mail address removed)

 

[David McRitchie]

Hi Thomas,

You can use COUNT to find out how many numeric entries
you have, and you can use MAX(0, calculation-sum) so you
do not have a negative number.

G2: =IF(COUNT(B2:F2)=5,MAX(0,SUM(B2:F2)),"")

Description in A, numeric values in B through F

You may use SUM for a total in G since SUM will ignore text entries
G20: =SUM(G$2:OFFSET(G20,-1,0))

 

[Guest]

If you have not yet entered values into A2 or A3, EXCEL assumes that they are
zero.
Try the following:
=if((A2*A3=0),"",(A2*3)+(A3)-1.5)

If you want the result in a cell to display zero if the expression is less
than zero, use MAX

=MAX(0,expression)

 

[David McRitchie]

Sorry I missed your formula, and is it a correct formula because
you are referencing two different rows. But between the two
responses you can probably figure out want you wanted.

Your question would be easier to read if it were like what we
would see on a spreadsheet rather than numbers and formula
scattered through a paragraph. After all that is what we would
have to do set up your problem and test it.

 

[thomasstyron]

Thank you to Gary’s Student and David for the help and to all others wh
were working on a solution for me.

And David, sorry about the format; yeah, looking back it probably woul
have been easier to read as if it was in a spreadsheet rather than i
paragraph form, but as this is my second question on this forum, I a
still learning. I’ll try to get it right in the future, just pleas
bear with me. I have been known to work hard rather than smar
sometimes.

And finally, the suggestions were great. Unfortunately now I hav
another problem as a result of my oversimplification of my equation
Here’s what I wanted to show a zero for a value of less than zero:

=IF(B2="Male",((4.95/(1.10938-(0.0008267*C8)+(0.0000016*((C8)^2))-(0.0002574*A2)))-4.5),((5.01/(1.0994921-(0.0009929*C8)+(0.0000023*((C8)^2))-(0.0001392*A2)))-4.57))

And I want to use Gary’s Student’s suggestion of usin
=MAX(0,expression). How do I combine two functions into one?

Thanks again,
Thoma

 

[David McRitchie]

Hi Thomas,
You plop your current formula where you have expression, I've used
extra spaces to make it easier to see. Thank goodness I don't have
to work with the formula. The only thing you have to worry about is
a limit of 7 nesting levels. You only had one now you have two.

=MAX(0,
IF(B2="Male",((4.95/(1.10938-(0.0008267*C8)+(0.0000016*((C8)^2))-(0.0002574*A2)))-4.5),((5.01/(1.0994921-(0.0009929*C8)+(0.0000023*(
(C8)^2))-(0.0001392*A2)))-4.57)) )

 

[thomasstyron]

That worked perfect!!! Now I have to study it and see how it worked; I
just copied and pasted it.

And again, I will work on how I post my questions and take pointers
from other members. And of course I have always searched the forum for
questions that have posted and would be similar to mine in order not to
duplicate questions and waste everyone's time. This place is so neat...

Thanks again.
Best regards,
Thomas Styron
(e-mail address removed)