How to remove the #value! result

[Javi]

Hi All, I can’t seem to get the #value! out.

I’m looking to have a 0 or keep the cell blank in cell M4 if cell K4 is blank. If I enter 0 in K4 then it returns a large vale in M4 because of the value in J4.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Thank you in advance for your help!
<o> </o>

Excel Workbook
	J	K	L	M
2	Start Date	Complete Date	Parts Procurement in Days	Total Process Days
3	10/1/2011	11/12/2011	2	42
4	11/8/2011		2	#VALUE!
Main

 

[barry houdini]

Try this formula in M3 copied down

=IF(COUNT(J3,K3)=2,K3-J3,"")

 

[Javi]

Thank you very much for your response the forumla worked perfectly.

I hate to take more of your time but I would appreciate it if you would explain the process of that formula.

 

[barry houdini]

COUNT counts numbers only (dates in Excel are formatted numbers) so the IF formula "says" if both J3 and K3 have numbers (COUNT(J3,K3)=2) subtract J3 from K3 (K3-J3), otherwise show a blank ("").

 

[Javi]

Thanks, it's starting to sink in,

What does the 2 represent?

=IF(COUNT(J3,K3)=2,K3-J3,"")

 

[barry houdini]

COUNT(J3,K3) counts the numbers in cells J3 and K3. Obviously the result can only be 0 1 or 2, it's 2 if both J3 and K3 are numbers

 

[Javi]

Got it!!!

Thank you for your detailed explanation I appreciate it.

 

[Javi]

Re: #2 on How to remove the #value! result

Could you please help me apply show blank for the formulas in Cells A3 and D3? As you can see I'm getting I number in a row that should be blank.

See row (4) below.

Thank You!

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										Edit Line	Edit Line
2	Days in House	Last Name	First Name	Status	Year	Make	Model	WorkOrder Number	Start Date	Parts Procurement and Schedule in Days	Hours	Complete Date	Total Process Days
3	39	Test 1	Test 1	35	2001	MD1	301	2401	10/1/2011	2	16	11/8/2011	38
4	40856			40856
5	9	Test 3	Test 3	5	2003	MD3	303	2403	10/31/2011	2	16	11/12/2011	12
6	7	Test 7	Test 7	3.5	2007	MD7	307	2407	11/2/2011	2	12	11/12/2011	10
Main

 

[barry houdini]

You can do a similar thing to the formulas I suggested earlier - you already know that TODAY() is a number so just check the other date, e.g. for A3 instead of

=TODAY()-I3

try

=IF(COUNT(I3)=1,TODAY()-I3,"")

 

[Javi]

Thank you,

That's exactly what I was using based on your earlier logic. However I was referencing a cell with text and not a numeric value when I put your formula and it worked. Is there a way to reference a cell with text?