Why the formula with #REF! still produces 0 value

Hello Sir,

I have a spreadsheet with a simple formula, for example, =SUM(1+((B2/C2)-1))*B1.

Now if I delete C2, then the formula became: =SUM(1+((B2/#REF!)-1))*B1

For some reason, the formula still produces a value as 0. Don't understand why because the automatic calculation is already on.

If this is tricky, is there a way to use a formula to detect if a formula contains specical characters such as #REF?

Thanks a lot in advance.

Originally Posted by Sambo kid

Just tried to reproduce your formula and entered stuff in the reference cells, got a value, then when I delete what is in your C cell reference I just get #DIV/0! Not sure why you are getting the ref error. You deleting the whole column?

No, I just deleted the cell only

Is there a way to use a formula to check if that cell's FORMULA contains any illegal error text, e.g. #REF!?

Do you have any circular references?

Is there a way to use a formula to check if another formula contains the sign #?

It will be very helpful.

Hi all,

I've found a macro to check if a formula contains any text:

Function GetFormula(cell_ref As Range) As String
If cell_ref.HasFormula Then
GetFormula = cell_ref.Formula
Else
GetFormula = ""
End If
End Function

This well resolves my problem. Thank you all