error message "#VALUE!" due to blank cells
1 Attachment(s)
Hi All,
I am trying to have a spreadsheet where i check the delivery time on orders (attached a picture). Essentially, it calculates the difference between the "Promised Date (A1)" and the "Atual Date (A2)" i.e. A1-A2 The problem is where is have blank cells with no information yet. then i get the nice error message "#VALUE!" Can anyone help in having these cells show nothing instead of the error message? Thank you! |
Quote:
It's simple enough to make the error message not show using: =IF(promised_date="","",promised_date-actual_date) and just swap promised_date and actual_date with the correct cell refs and copy down. |
error message "#VALUE!" due to blank cells
Are the cells really empty?
Maybe they have spaces in them? When you hover over the error-checking arrows do you get a message? What happens when you clear those cells? Gord On Wed, 4 Apr 2012 15:17:35 +0000, Dorian74 wrote: Hi All, I am trying to have a spreadsheet where i check the delivery time on orders (attached a picture). Essentially, it calculates the difference between the "Promised Date (A1)" and the "Atual Date (A2)" i.e. A1-A2 The problem is where is have blank cells with no information yet. then i get the nice error message "#VALUE!" Can anyone help in having these cells show nothing instead of the error message? Thank you! +-------------------------------------------------------------------+ |Filename: PO Date.JPG | |Download: http://www.excelbanter.com/attachment.php?attachmentid=318| +-------------------------------------------------------------------+ |
error message "#VALUE!" due to blank cells
Hi
Try this =IF(A1="","",B1-A1) change ranges to suit HTH Mick. |
error message "#VALUE!" due to blank cells
"Dorian74" wrote:
I am trying to have a spreadsheet where i check the delivery time on orders (attached a picture). For future reference, images that only duplicate what you describe in English is usually useless. What we need to know a formulas, cell values, etc. But in this case, the problem is "obvious" -- or so it would seem. "Dorian74" wrote: Essentially, it calculates the difference between the "Promised Date (A1)" and the "Atual Date (A2)" i.e. A1-A2 The problem is where is have blank cells with no information yet. then i get the nice error message "#VALUE!" Can anyone help in having these cells show nothing instead of the error message? That cells that __appear__ blank probably are not "empty" (no constant and no formula). Instead, probably (hopefully) their value is the null string (""). Alternatively, their value might strings of spaces or non-breaking spaces (HTML  ). In either case, the following should avoid the #VALUE error: =IF(COUNT(A1,A2)<2,"",A1-A2) formatted as General or Number to avoid a "negative date" error in the case when "Actual" is later than "Promised". Thus, the cell will __appear__ blank unless A1 and A2 have numeric values, in this case Excel dates. In that case, the value is the null string (""). |
Quote:
|
All times are GMT +1. The time now is 12:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com