Formula #VALUE! when blank cell.

[rmorg]

What i want the formula to do is take the calculated cumulative weight loss in kgs from column F and convert it to Stones and pounds in Column G.

It correctly does the conversion BUT until there is an entry in column F. (ie. while column f is blank for future weeks) then it shows #value ! in Column G

the formula i have used is...

=IF(OR(ISBLANK(F8),ISBLANK(#REF!)),"",(INT(CONVERT(F8,"kg","lbm")/14)&" st "&ROUND(MOD(CONVERT(F8,"kg","lbm"),14),0)&" lbs "))

 

[SpillerBD]

Just wrap your existing formula in an IFERROR or use an IF to see if that trigger value is blank.
IFERROR is often easier for the next user to read and understand, but if you have a huge table for this formula, you'll have slightly beter performance use a IF to check for BLANK in "F"

 

[joeu2004]

Use F8="" instead of ISBLANK(F8). The latter is true only if the cell has no value (no formula as well as no constant). The null string ("") is a value.

Of course, you also need to fix the #REF error (!). Replace E8 below.

=IF(OR(F8="",E8=""), "", INT(CONVERT(F8,"kg","lbm")/14) & " st " & ROUND(MOD(CONVERT(F8,"kg","lbm"),14),0) & " lbs")