excluding cells from the formula when empty

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am designing a cost spreadsheet for recipes, i need to exclude cells from a
formula when, no data is entered, then i need to include the cell when data
is entered.
Input (b13) Input2 (h13) Result
0 0 #Value!
When i enter =sum(b13*h13) i get #Value!

If anyone understands my problem, PLEASE HELP
 
First, your SUM() function is not needed,

=B13*H13

returns the same result.

If b13 and h13 really have a "0" entry, then the reason you're getting
the #VALUE! error is that the input(s) were entered as Text rather than
numbers. In that case, change the format to General or a Number format,
and reenter the zeros.

If they are "blank", instead, then you probably cleared them using the
space bar, which inserts a text character, and that is causing the
#VALUE! error. Clear the cells using the Del key.

You can work around these problems by using

=IF(COUNT(B13,H13) =2, B13*H13,"")

which will display a null string (looks like a blank cell) if B13 and
H13 do not contain numbers. However, realize that that may give you a
false blank if one of the inputs is entered as Text.
 
Not sure why you are getting an error in that formula. That error usually
happens when you are using an incorrect Argument or Operator in a formula.
Are you trying to add or multiply?

You dont' really need the SUM Function the way you have your formula written.
Why not use =B13*H13?

If the Error persists, you could try:
If(Iserror(B13*H13),"",B13*H13)
This won't fix the error, but it will mask it when it occurs.

tj
 
Joe said:
i am designing a cost spreadsheet for recipes, i need to exclude cell
from a
formula when, no data is entered, then i need to include the cell whe
data
is entered.
Input (b13) Input2 (h13) Result
0 0 #Value!
When i enter =sum(b13*h13) i get #Value!

If anyone understands my problem, PLEASE HELP
Click to expand...

It seems B13 and H13 are not really empty but contain text, probably
formula blank. If you can't avoid text values in these cells, try:

=N(B13)*N(H13
 
Back
Top