@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.
