How to remove the #value! result

Javi

Active Member
Joined
May 26, 2011
Messages
438
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-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Thank you in advance for your help!
<o:p> </o:p>
Excel Workbook
JKLM
2Start DateComplete DateParts Procurement in DaysTotal Process Days
310/1/201111/12/2011242
411/8/20112#VALUE!
Main
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Javi

Active Member
Joined
May 26, 2011
Messages
438
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
May 26, 2011
Messages
438

ADVERTISEMENT

Thanks, it's starting to sink in,

What does the 2 represent?

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

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
May 26, 2011
Messages
438

ADVERTISEMENT

Got it!!! :):):)

Thank you for your detailed explanation I appreciate it.
 

Javi

Active Member
Joined
May 26, 2011
Messages
438
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
ABCDEFGHIJKLM
1Edit LineEdit Line
2Days in HouseLast NameFirst NameStatusYearMakeModelWorkOrder NumberStart DateParts Procurement and Schedule in DaysHoursComplete DateTotal Process Days
339Test 1Test 1352001MD1301240110/1/201121611/8/201138
44085640856 
59Test 3Test 352003MD3303240310/31/201121611/12/201112
67Test 7Test 73.52007MD7307240711/2/201121211/12/201110
Main
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Active Member
Joined
May 26, 2011
Messages
438
Thank you,

That's exactly what I was using :biggrin: 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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top