Stop calculating on an error

You can build error traps into your original formulas, but doing so is problematic. You yourself are using the errors to investigate backward through formulas looking for the source of the error. If you disable or suppress the errors, knowing that something is broken is less clear, as is HOW.

You sure you want to do this?

I suppose the error traps themselves could be designed to point to the problem. Post up some sample formulas that are breaking and we can offer some error trap suggestions appropriate to them.

I certainly don't want to supress any errors, the kind of thing I am talking about comes from a typo of some sort. like I type the name of a sheet I am referencing incorrectly or something like that. I assume that Excel calculates one cell at a time and cells that depend on other cells get calculated in some sort of sequence. So if, rather than wait 20 minutes for the whole thing to calculate with errors, if it stopped when it hit thefirst formula that was going to end up #N/A and show me that cell, I could fix that error and continue. Like stopping the compiling of code to fix the discovered error prior to continuing with the compilation. Sounds like it is not really possible, though?

I don't think so, Once calculation starts, it's going to finish..

So if, rather than wait 20 minutes for the whole thing to calculate with errors

Click to expand...

If that's a literal example of time it takes to calculate, you would be better off concentrating on why it takes so long to calculate, and how to fix it.

What kind of formulas do you have?

One big No-No in formulas is to refer to an ENTIRE column, like =SUM(A:A). You're far better off defining the range =SUM(A1:A100)

Also, if your formulas use TODAY() or NOW() alot, you can put those into single cells, and have your all your formulas refer to that one cell. That will make calculation faster.

Exaple, if you have alot of formulas like

=IF(A1<TODAY(),TRUE,FALSE) p calculate to has it times 1000 that?s rows, for down filled and Today().<> < TODAY())
But if you put =TODAY() in single cell, say Z1 for example, you could change that formula to
=IF(A1<$Z$1,True,False) and fill down.
Now Today() will only be calculated once.

Lots of general things like that you can do to improve calculation speed.

I can't think of a simple way of stopping on an error, other watching and pressing Ctrl/Break.

Jonmo,

If you time the calculation you will find that many built-in functions like SUM() are fast at processing whole columns. Just don't use whole columns in array formulae or SUMPRODUCT.

Also you won't in general save much calculation time by moving volatile functions like TODAY() or NOW() out to separate cells because dependents of volatile cells get recalculated every time the volatile cell gets recalculated.

Another common HUGE calculation speed HOG is Vlookup.

Using Index and match instead is much more efficient.
Especially if you have alot of vlookups, and you return several columns of data based on the same matching value.

See this sample.
The match (that looks up the value in the first column) can be put in a seperate column, so it can be used by each formula in the row. So the lookup is only done once per row. Then use index based on that match.

Hope this helps...

Both Example sets of formulas use the same set of data, but Example 2 will be MUCH MUCH MUCH more efficient.

I have about a gazillion of these two kinds of formulas:
=INDIRECT("[BoxRest.xls]Light!"&ADDRESS(MATCH(I4,[BoxRest.xls]Light!$B:$B,0)+1,MATCH($B4,INDIRECT("[BoxRest.xls]Light!"&MATCH(I4,[BoxRest.xls]Light!$B:$B,0)&":"&MATCH(I4,[BoxRest.xls]Light!$B:$B,0)),0)-1))

=(INDIRECT(ADDRESS((ROW(J5)),(COLUMN(J5)-2)))+50*((IF(INDIRECT(ADDRESS((ROW(J5)-1),(COLUMN(J5))))="W",1,(IF(INDIRECT(ADDRESS((ROW(J5)-1),(COLUMN(J5))))="L",0,0.5))))-(1/(10^((INDIRECT(ADDRESS((ROW(J5)),(COLUMN(J5)-1)))-INDIRECT(ADDRESS((ROW(J5)),(COLUMN(J5)-2))))/500)+1))))
spread over two workbooks
a bunch of these
=COUNTIF($F6:$IV6,"w")
and these
=CONCATENATE($C6,"-",$D6,"-",$E6)
and these
=IF(A9="x",INDIRECT(ADDRESS(ROW(E9),D9+7,4)),"Done")
and these
=IF(ISNUMBER(F7),RANK(F7,F:F),RANK(A7,A:A))

If you time the calculation you will find that many built-in functions like SUM() are fast at processing whole columns. Just don't use whole columns in array formulae or SUMPRODUCT.

Click to expand...

ANY formula will be faster if you use a designated range, instead of the full column.
The effect is more noticable in some formulas than others, but it is fact that SUM(A1:A1000) will be faster than SUM(A:A). It only has to go through 1000 cells instead of 65536 Cells.

Also you won't in general save much calculation time by moving volatile functions like TODAY() or NOW() out to separate cells because dependents of volatile cells get recalculated every time the volatile cell gets recalculated.

Click to expand...

Yes, each formula that depends on the cell containing Today() will get recalculated every time (that's true for any formula)....BUT, the time saver is in the fact that TODAY() only get's calculated once. Where before, TODAY() would be calculated however many times it appears in all other formulas.

Each formula referring to the cell with TODAY() will calculate more frequently. BUT, Each formula will calculate faster because they don't ALL have to calulate TODAY()

So 101 formulas calculating at .001 seconds every time something changes, is better than 100 formulas calculating at .01 seconds every time something changes.

ervierto,

Answer is still the same, you can't stop calculation upon finding an error.

All those inderect formulas are definately the bottleneck in speed.
I'm sure at least the 1st and 3rd formulas containing Inderect can be improved.
But it's hard to say without knowing what the purpose is, and the underlying data.

You can email me the file (replacing personal data with dummy sample data of coarse), if you like.

PM me for my email...

The file is too big to email (something like 120 meg or so...) but, essentially what the first formula is attempting to do is this:
A B C D E F
1 Joe Ted W Fred L
2 500 Formula 1A Formula 2 Formula 1 Formula 2
3 Ted Joe L
4 500 Formula 1 Formula 2
5 Fred etc...
6 etc...
7 Sam etc....
8 etc...
So, Formula 1 and Formula 1A are the same, I am just using the A for convenience in explaining.
Formula 1 A gets the information from the cell above it (Ted), matches that value with a value from the column with the names (Column A), Looks in the row where it found the value (Ted, Row 3) to find the value from the name column where it was (Joe, C3) and returns the value one column to the left and one row below (500).


For the 3rd formula you mentioned, that is just counting the number of w's in the row.