Stop calculating on an error

[ervierto]

I have a workbook that takes a long time to calculate. A lot of the values are interrelated and if there is an error in one of the primary formulas, it will propagate through the whole worksheet. It then takes a while to find the error and after I correct it, I then have to go through the whole calculation process again. Is there a way to have Excel stop calculation when it first finds an error?

 

[Jonmo1]

Today() only takes a few microseconds: you won't be able to detect the difference between calling it once and calling it 101 times.

10000 formulas is probably closer to the context of the thread, since the OP said "A gazillion"...

This macro ran in 1 minute 3 seconds

Sub test()
Application.ScreenUpdating = False
x = Now
For i = 1 To 10000
Range("A" & i).Formula = "=TODAY()"
Next i
MsgBox Format(Now - x, "hh:mm:ss")
Application.ScreenUpdating = True
End Sub

This one ran in 20 seconds.

Sub test()
Application.ScreenUpdating = False
x = Now
Range("B1").Formula = "=Today()"
For i = 1 To 10000
Range("A" & i).Formula = "=B1"
Next i
MsgBox Format(Now - x, "hh:mm:ss")
Application.ScreenUpdating = True
End Sub

That seems like quite a significant difference to me..

 

[FastExcel]

Jonmo,

Whole Column References:
I agree its possible to go overboard with whole column references, but in many cases they are significantly faster to calculate than the alternative dynamic range names, and work successfully with most Excel functions that accept ranges as input.
So I think its a mistake to rule them out just because they are bad news/dont work with array formulae and SUMPRODUCT etc.

Your Timing Tests:
Its a bit misleading to include the VBA and 10000 automatic recalculations!

I would suggest you download and install my RangeCalc addin from
http://www.decisionmodels.com/downloads.htm

create 2 columns of formulas in an empty workbook, switch to manual calculation, select a column and click the rangecalc button a few times.

On my system I get times of 0.0107 and 0.0063 seconds respectively for 10000 formulae: not worth bothering with.

Of course I agree with you about minimising unneccessary duplicated calculations, see my white paper at:
http://msdn.microsoft.com/en-us/library/aa730921.aspx
and my website.

But giving an example based on trivial volatile functions like NOW() and TODAY() may waste unsuspecting peoples time and also mislead them into thinking that they can avoid the volatility penalty by sticking volatile functions in a separate cell.

 

[Jonmo1]

Whole Column References:
I agree its possible to go overboard with whole column references, but in many cases they are significantly faster to calculate than the alternative dynamic range names, and work successfully with most Excel functions that accept ranges as input.
So I think its a mistake to rule them out just because they are bad news/dont work with array formulae and SUMPRODUCT etc.

I never said anything about using dynamic named ranges. That is not the only alternative.
To me, the best alternative is to take a number that is larger than the number of rows you would ever expect your data to be. And use that.

And in most cases, there is such a number. If your data is in the range of 10000 rows, but can fluxuate +/- 1000, use 15000. If your data is in the range of 1000 rows, but can fluctuate +/- 100, use 2000.


And I did install your addin (very nice by the way).
Turned off calculation
Column B is blank
In A1:A10000 I put =SUM(B:B), it took 65 Miliseconds.
In A1:A10000 I put =SUM(B$1:B$1000), it took 12 miliseconds.

How can you say B:B is faster than (or even equal to) B1:B1000 ?
Yes, it's a small difference, but there IS a difference. Therefor I can only conclude that SUM indeed DOES look at ALL cells in the range weather they are empty or not.

 

[FastExcel]

Whole Column Refs:
I guess its a matter of taste: I don't like hard-coded limits.

Glad you like RangeCalc: very useful for exploring Excel's calculation quirks.

Try comparing the whole column and 10000 cases when summing column A and it has either 10000 numbers and the usedrange ends at 10000 or it has 64000 numbers.
On my system the whole column summing 64000 numbers takes a lot longer than whole column summing 10000 numbers.

Also on my system running your test with a blank B column both 10000 SUMs take 60 millisecs

(I used Excel 2003 by the way)

 

[Jonmo1]

ervierto,

The problem with getting rid of the indrect address business is that when I change the location of the cells, the references are inconsistent. For example, if I use this formula:

=(H5)+50*((IF(J4="W",1,(IF(J4="L",0,0.5))))-(1/(10^((I5-H5)/500)+1)))
and insert another two column two row set between H and J, the reference to H does not change.

I've been giving this alot of thought...

Is there a column Header or something else that can be used to determine which column H5 should refer to? You could use an INDEX(MATCH formula to get the correct cell.

 

[ervierto]

It's just supposed to refer to the cell one to the left of the cell the formula is in.
A B C
Fred Joe W
500 400 Formula
I want that "Formula" to do a calculation based on 500, 400 and W.
If I insert another set,
A B C D E
1 Fred Ted L Joe W
2 500 300 Formula result (200) 400 Formula

So the formula in C2 uses A2 and B2. The formula in E2 uses C2 and D2, etc.

 

[Jonmo1]

On my system the whole column summing 64000 numbers takes a lot longer than whole column summing 10000 numbers

Of coarse it does. Because it's actually doing something (adding numbers). If the cells are blank, no addition is performed, the cell is just skipped. But excel STILL MUST look at the cell, it just doesn't have to do anything with it.

 

[Jonmo1]

Well ervierto, I'll go back to the "Pick your poison" thing....

Are you sure there's no other means that can be used to determine which column the the first address should refer to? Can you CREATE a method? Add a header, or even an X or something in the bottom row (or 1 below) in that column?

 

[Jonmo1]

Try this, this may help...

Click Insert - Name - Define
give a name like MyRow
put =GET.CELL(2,INDIRECT("RC",FALSE))
Click Add

Make another one called MyCol
=GET.CELL(3,INDIRECT("RC",FALSE))

Now anywhere in your formula that refers to Row or Column, replace that with MyRow and MyCol

Those will return the Row# and Column# that the formula is entered in.

Not sure, but I think I've seen others do this and it improved performance.

You can also replace Indirect with INDEX as explained before..

 

[Jonmo1]

=(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))))


Can be changed to This

=(INDIRECT(ADDRESS(myrow,mycol-2))+50*((IF(INDIRECT(ADDRESS(myrow-1,mycol))="W",1,(IF(INDIRECT(ADDRESS(myrow-1,mycol))="L",0,0.5))))-(1/(10^((INDIRECT(ADDRESS(myrow,mycol-1))-INDIRECT(ADDRESS(myrow,mycol-2)))/500)+1))))

the row and column is only calculated once in the named range MyRow. Same for the column.

Now you can also remove the Indirect and Address parts, and use Index instead

=INDEX($A$1:$AZ$10000,myrow,mycol-2)+50*((IF(INDEX($A$1:$AZ$10000,myrow-1,mycol)="W",1,(IF(INDEX($A$1:$AZ$10000,myrow-1,mycol)="L",0,0.5))))-(1/(10^((INDEX($A$1:$AZ$10000,myrow,mycol-1)-INDEX($A$1:$AZ$10000,myrow,mycol-2))/500)+1)))

$A$1:$AZ$10000 = your entire data set.