#Value! when fields have no value

%3CLINGO-SUB%20id%3D%22lingo-sub-3242020%22%20slang%3D%22en-US%22%3E%23Value!%20when%20fields%20have%20no%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3242020%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20spreadsheets%20with%20the%20same%20formula%20in%20one%20column%20ao%20each.%26nbsp%3B%20The%20formula%20is%20%3DIF(BU6%26lt%3B1%2C%22%22%2CIF(BU6%26gt%3B2%2CBU6%2B7))%3C%2FP%3E%3CP%3EI%20get%20%23Value!%20for%20cells%20that%20have%20no%20value%20on%20one%20sheet%20but%20no%20on%20the%20other%3F%3F%3F%26nbsp%3B%20How%20do%20I%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3242020%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3242900%22%20slang%3D%22en-US%22%3ERe%3A%20%23Value!%20when%20fields%20have%20no%20value%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3242900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1322078%22%20target%3D%22_blank%22%3E%40Debra_C%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20check%20if%20there%20is%20a%20space%20in%20cell%20BU6%3F%20In%20the%20attached%20example%20i%20entered%20a%20space%20in%20BU6%20and%20the%20formula%20returns%20%23VALUE!.%20As%20soon%20as%20i%20delete%20the%20space%20the%20formula%20works%20as%20intended.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have two spreadsheets with the same formula in one column ao each.  The formula is =IF(BU6<1,"",IF(BU6>2,BU6+7))

I get #Value! for cells that have no value on one sheet but no on the other???  How do I fix this?

 

 

2 Replies

@Debra_C 

Can you check if there is a space in cell BU6? In the attached example i entered a space in BU6 and the formula returns #VALUE!. As soon as i delete the space the formula works as intended.

@Debra_C 

 

=IF(N(BU6)<1, "", IF(BU6>2, BU6+7))

 

is probably sufficient to avoid the #VALUE error.  But your formula returns FALSE for BU6>=1 and BU6<=2.  Usually, that is undesirable.

 

The root cause of the problem is that BU6 does have a value.

 

The value is text that appears to be blank.  Confirm with the formula =ISTEXT(BU6).  Looks can be deceiving; and the format of the cell does not matter.

 

The N() function returns zero if the parameter is non-numeric; otherwise, it returns the numeric value of its parameter.

 

BU6 might be a formula that returns the null string (""), just like your formula above.

 

Alternatively, someone might have copy-and-pasted-special-value into BU6, replacing the formula with a constant null string.  Consequently, the cell might appear to be empty (in the Formula Bar, for example), but it is not.

 

Alternatively, the text value in BU6 might be one or more characters that appear to be blank.  Those include normal spaces (ASCII 32), nonbreaking spaces (ASCII 160), tabs (ASCII 9) and any  of the nonprinting control characters (ASCII values less than 32 and ASCII 127).  Some ASCII codes > 127 also appear to be blank.

 

You might choose to delete the text value in BU6 instead of using the N() function.