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" wrote in message
news:thomasstyron.1szy2a_1122750301.7167@excelforu m-nospam.com...
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
--
thomasstyron
------------------------------------------------------------------------
thomasstyron's Profile: http://www.excelforum.com/member.php...o&userid=25568
View this thread: http://www.excelforum.com/showthread...hreadid=391561