Value of Less Than Zero to Equal Zero

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
[email protected]

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)
--
Gary's Student


"thomasstyron" wrote:

>
> 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
> [email protected]
>
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=391561
>
>

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))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"thomasstyron" <[email protected]> wrote in message
news:[email protected]...
>
> 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
> [email protected]
>
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=391561
>

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.

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

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

And finally, the suggestions were great. Unfortunately now I have 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 using =MAX(0,expression). How do I combine two functions into one?

Thanks again,
Thomas

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)) )
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"thomasstyron" <[email protected]> wrote in message
news:[email protected]...
>
> Thank you to Gary’s Student and David for the help and to all others who
> were working on a solution for me.
>
> And David, sorry about the format; yeah, looking back it probably would
> have been easier to read as if it was in a spreadsheet rather than in
> paragraph form, but as this is my second question on this forum, I am
> still learning. I’ll try to get it right in the future, just please
> bear with me. I have been known to work hard rather than smart
> sometimes.
>
> And finally, the suggestions were great. Unfortunately now I have
> 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 using
> =MAX(0,expression). How do I combine two functions into one?
>
> Thanks again,
> Thomas
>
>
> --
> thomasstyron
> ------------------------------------------------------------------------
> thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
> View this thread: http://www.excelforum.com/showthread...hreadid=391561
>

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
[email protected]