MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Null or 0 returns on formula errors.


Posted by John Stretton on January 10, 2002 11:34 AM

I have the following formula in a form that I use to track machine output (the formula was created with the formula wizard.)

=INDEX('C:\My Documents\[Formulas.xls]price matrix'!$A$1:$H$100, MATCH(J7,'C:\My Documents\[Formulas.xls]price matrix'!$A$1:$A$100,), MATCH(I7,'C:\My Documents\[Formulas.xls]price matrix'!$A$1:$H$1,))

The template also does further calculations on the results of this formula. But the formula as written returns #N/A as a result if either J7 or I7 is null (no entry.) How can I edit this so that it returns a 0 (zero) value, if J7 or I7 is empty?


Posted by Gary Hewitt-Long on January 10, 2002 11:39 AM

Try using IF and ISBLANK in the formula i.e.

IF(ISBLANK(J7),"0","THE REST OF YOUR FORMULA HERE")

Regards

Gary Hewitt-Long

Posted by John Stretton on January 11, 2002 6:34 AM

That worked - thanks