ExcelBanter - excluding cells from the formula when empty

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - excluding cells from the formula when empty (https://www.excelbanter.com/excel-worksheet-functions/6864-excluding-cells-formula-when-empty.html)

excluding cells from the formula when empty

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.

In article ,
Joe Shell <Joe wrote:

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!

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 Shell" wrote:

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

Joe Shell Wrote:
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

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

=N(B13)*N(H13)

--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=319158