If your formula is long and has a lot of them an Iferror would calculate 2 times longer. Since once to check the formula if it has an error and another to do the formula.
Hi ,
That is not how IFERROR works , to my knowledge , though I may be wrong.
When you wrap a complex formula within IFERROR , the complex formula is evaluated only once.
The problem is that the complex formula may error out on much simpler conditions such as some cell being blank or containing 0 ; in such conditions , it is better to prevent the evaluation of the complex formula by using an IF to exclude the blank cell or cell containing 0 conditions.
Using an IF instead of an IFERROR will certainly cause the evaluation of the complex formula twice , once to check for the error condition , and once for the result in case the error condition does not exist.
Let us assume that you have a simple formula such as :
=VLOOKUP(A1 , B1:C10 , 2 , FALSE)
In case A1 is blank or does not exist in the range B1:B10 , the above formula will return the error value #N/A.
Thus , to avoid this result in the cell , we can use either of the following approaches.
1. Use an IF to exclude the two conditions :
=IF(ISNA(VLOOKUP(A1 , B1:C10 , 2 , FALSE)) , "" , VLOOKUP(A1 , B1:C10 , 2 , FALSE))
=IF(ISNA(MATCH(A1 , B1:B10 , FALSE)) , "" , VLOOKUP(A1 , B1:C10 , 2 , FALSE))
=IF(A1 = "" , "" , IF(ISNA(VLOOKUP(A1 , B1:C10 , 2 , FALSE)) , "" , VLOOKUP(A1 , B1:C10 , 2 , FALSE)))
=IF(A1 = "" , "" , IF(ISNA(MATCH(A1 , B1:B10 , FALSE)) , "" , VLOOKUP(A1 , B1:C10 , 2 , FALSE)))
All of the above approaches evaluate a formula twice ; in some cases , the evaluation can be avoided altogether if the error conditions are easier to evaluate using only an IF , as for example in the following :
=IF(A1 = 0 , "" , B1/A1)
where the evaluation of the B1/A1 part does not happen when A1 is 0.
2. Use an IFERROR :
=IFERROR(VLOOKUP(A1 , B1:C10 , 2 , FALSE)) , "" )
In this formula , the VLOOKUP part is always evaluated ; if it results in an error , the cell displays a blank , else it displays the result of the already evaluated VLOOKUP function.
It can never happen that the VLOOKUP part is evaluated twice ; there is no reason to do so.
The only point about using the IFERROR is that the VLOOKUP part will be evaluated even when there is no reason to do so , but you need to see what percentage of your data will result in an error result ; if this percentage is small , then the benefit of using an IFERROR is more than the disadvantage of having the VLOOKUP part evaluated all the time.
Narayan