Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Joe Shell
 
Posts: n/a
Default 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
  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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!

  #4   Report Post  
tjtjjtjt
 
Posts: n/a
Default

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

  #5   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i select different cells to be use in a formula. Manuel Excel Discussion (Misc queries) 1 January 13th 05 04:52 PM
CountIF cells are not empty Wayne Excel Discussion (Misc queries) 3 January 6th 05 05:44 PM
Sumproduct ... Empty Cells vs Spaces? Ken Excel Discussion (Misc queries) 9 December 17th 04 09:03 PM
Empty Cells, Spaces, Cond Format? Ken Excel Discussion (Misc queries) 3 December 4th 04 05:47 PM
A formula to check differrent cells Carlsbad Excel Worksheet Functions 0 October 28th 04 01:19 AM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"