• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Avoid iferror if you want speed, so use If instead.

Harry0

Member
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.
Best be creative to use other alternatives like IF function or other functions.
or better yet mess with the options to disable or replace error reporting to leave it blank, which I can't say off the top of my head now.

If would have been good if excel comes with a warning when typing it.
Maybe in another 40 years when Lotus 123 comes back... I'm joking.
 
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
 
I agree - the claim is unfounded as far as I can see.

Harry0 - where is your proof, please?
 
That is what a expert MVP MS Excel author ExcelIsFun says when using it with arrays
If not I guess every expert has their own view.
Or maybe I am not understanding him.
As for the formula above you can use a helper cell.
=if(a1="","",VLOOKUP(A1 , B1:C10 , 2 , FALSE))
I did a sheet that used over 2,000,000 active formulas and I did not use iferror or if to deal with errors as much but used the options to set error to zero or to not report them.

Also plenty of people in the forum says to use index match instead of the volatile vlookup, despite vlookup is 1 function and index match is 2 functions.
 
Last edited:
Hi ,

Can we first agree that IFERROR does not calculate the complex formula around which it is wrapped twice ?

Every expert cannot have their own view on everything ; they can have their own view on things which are not settled conclusively.

All experts agree that Option Explicit should be used.

All experts agree that named ranges are good and should be used.

All experts agree that tables are good and should be used where ever possible ; shared workbooks do not allow the use of tables.

Mike Girvin has clearly indicated that an IFERROR is not to be used IF .... ; I think you have not mentioned those circumstances. Those circumstances are where there are ways of avoiding the IFERROR , without using other equally compute intensive formulas.

The situation explained in his video is a typical one ; in one case the complex formula using SMALL(IF is going to be executed only 48 times , while in another case the same will be executed thousands of times. How do we avoid calculating the complex formula those thousands of times when we want it to be executed only 48 times ?

Hence the use of a separate cell with a COUNTIF formula ; once we know how many times we need to execute a complex formula , we do not need an IFERROR to avoid displaying error results. We can use an IF instead.

As for using display options to not display error results , I have no idea what options these are ; probably you can explain this.

Narayan
 
You might be right, but I cant say without solid evidence when 2 experts say otherwise, but to see test done to be sure which is best. But I dont think iferror can beat at least example 1 below.

I cant test or remember off the top of my head how I did it since its been years ago how I did it, but from looking quickly online others state it.

1 turning off error checking
https://excelribbon.tips.net/T010655_Turning_Off_Error_Checking.html

OR
2 Apply the conditional format
https://support.office.com/en-us/ar...in-cells-d171b96e-8fb4-4863-a1ba-b64557474439

I had to use these as stated since "if" and "iferror" were just too time consuming to process too many formulas at the same time. Iferror is fine for small things but not for complicated long arrays all over so if was used on other aspects. Even then I it could have taken at least 10 seconds to process so many array formulas sometimes longer depending how quirky excel (for mac) acted. Despite i been using spread sheets for over 30 years, I am not an expert like you, but as for now I will stick to my original title based off my experience. But obviously their are certain conditions as as always.
 
Hi ,

The above has nothing to do with formulas themselves and the speed of recalculation ; this is a feature of Excel which the user can use to alert for certain conditions. I quote from the above article :

If an error is located (or, at the least, what Excel thinks is an error), then the cell is "flagged" with a small green triangle in the upper-left corner of the cell.
Excel will not turn off the display of error results nor will the highlighted cells display blanks because of this feature.

As for your second point :
conditional formatting is also compute intensive and contributes to increasing the workbook file size. Whether the computation speed is affected by conditionally formatting all cells to highlight a few error cells , only tests can say.

Narayan
 
Every expert cannot have their own view on everything ; they can have their own view on things which are not settled conclusively.

All experts agree that Option Explicit should be used.

All experts agree that named ranges are good and should be used.

All experts agree that tables are good and should be used where ever possible ; shared workbooks do not allow the use of tables.

Unless you define an expert to be someone who agrees with those statements, I'd have to disagree. Many, or even most, might agree with one or more of those statements, but it is certainly not true that they all agree with all of those.
 
Unless you define an expert to be someone who agrees with those statements, I'd have to disagree. Many, or even most, might agree with one or more of those statements, but it is certainly not true that they all agree with all of those.
Hi ,

I will agree with you if you can cite some experts who disagree with those three statements.

I would like to know their reasons for not agreeing with the three statements.

Narayan
 
There's a guy who posts as snb who is an extremely good coder (even if I can't stand his style) who never uses Option Explicit. And Bob Phillips is not a huge fan of tables (other than when using Power Query/ Power Pivot). I try to avoid ever using tables with anything other than small data sets, though whether I count as an expert is open to debate.
 
Hi ,

I do not go by personal practices.

I go by those who write blogs , and from what I have read , and I can cite those blog posts if you want , all of them agree with those three statements , which is why I selected those three.

snb is a genius in Excel , but I doubt if he himself will recommend that others follow his footsteps.

I would be interested to know why Bob Phillips does not use Tables ; not using Tables does not necessarily imply that he is against using them.

But anyway , let us not debate these three points ; I am no expert , and I am going strictly by those who are MVPs in Excel , and who write blogs and who favour these points I mentioned.

I certainly never meant to say that anyone who did not agree with these points is not an expert.

As for my use of the word all , it was more to emphasize that there are some points which are controversial and have experts on both sides , and there are matters on which there is a general consensus as to what is the best practice.

There will always be experts who will go against the grain , but following them would then be a matter of personal opinion , rather than because they are following best practices.

Narayan
 
Best practices are inherently opinion based though. ;) Anyway, in my opinion, if at least two former MVPs are sceptical of tables, it's a bit of a stretch to say "All experts agree that tables are good and should be used where ever possible".
 
Back
Top