Recalculate options ?

Hi Andonny

Let me guess, you have a load of array formulas ?

It's not possible to recalculate a range only. I could write some pretty involved VBA to do so but it would far better if we get to the route of your problem.

Dave
OzGrid Business Applications

Hi Dave,
You guessed correctly and maybe you recomment to reduce them or find a better solution. What I am doing is still at the developing stage so I was hoping that I can take a shortcut until I get a chance to think about improveing it.

Andonny

I'm afraid not Andonny, this is what annoys me on this site. Two unamed people keep posting array formulas, when 95% of the time they shouldn't! Not only that, they know the long consequences of what they are doing.

All I can suggest is to look at the database functions.

Dave
OzGrid Business Applications

Commiseration

Saw your post and couldn't pass it up without offering my experiences in a similar vein (though you probably know all of this stuff). I too started out working with large formulae-ridden spreadsheets, and, in the course of my data analysis, Excel began routinely punishing me with long recalculation times and, occasionally, incomplete calculations and mis-sorts.

One thing that helped was to limit the calculations to the number of digits needed. I I routinely reduced the DISPLAY of significant digits, but the calculations do not use the displayed digits for calculations (instead it uses "full" precision). If you click "Tools"-->"Options" and select the "Calculation" tab, check the "precision as displayed" box this can shave some time off of your calculations.

The thing that really made a difference for me was to copy my entire sheet (typically 40 columns X 8800 rows) to a new file, pasting as values ("edit"-->"paste special" click "values". Then recopy the formula area with which you want to play into the new spreadsheet at its original coordinates. Close the original workbook or use Shift+F9 to calculate only the sheet you're on. Once you're happy with your data, you can move the modified formulas and values back to the original sheet and just do the massive calculation once.

I'd also add that I've since gotten away from spreadsheet designs in which I ask Excel to do so much in a single calculation "wave". As I said earlier, I have had it give some anomalous results and/or hang during these massive requests and I'm much more confident with my data broken apart in spreadsheet chunks.

Good luck and my sympathy!

Re: Can be done...

Andonny,

You can calculate a single range in Excel, or at least in Excel 2000. I don't know if Excel 97 supports it. For example if you have formulas in the columns A1-A10 and B1-B10 (I used the formula =int(rand()*1000) for my check) and then use a macro like this:-

Sub Calcrange()
Range("b1:b11").Calculate
End Sub

Obviously, you could connect this to a worksheet event so that this small range is recalculated each time you change a cell, for example.

HTH,
Dax.

Both Dax and Eric posted constructive proposals.

You can find a thread at this site specifically on this issue that took place a while back.

By the way, array formulas are not the sole reason for recurring recalcs. In my opinion, globally defined named dynamic ranges and a set of volatile functions constitute a signficant cause of recalcs. Another, which seems to escape the attention, is having of loads of VBA code (Macro's etc).

FYI, the following are the so-called volatile functions:

AREAS
CELL
COLUMNS
INDEX
INDIRECT
NOW
OFFSET
RAND
ROWS
TODAY

It's a good practice, it seems to me, not to imbed COLUMNS, ROWS, NOW, TODAY directly in formulas. For example:

=IF(A1<=TODAY(),1,0) copied down say to A5000

can better be rewritten.

Put =TODAY() in B1 and change the IF-formula to:

=IF(A1<=B1,1,0).

Regarding the array formulas, some of these can be replaced by an elaborate system of formulas, but not all.

I recently gave you an array formula (see 17939.html), which was:

=SUM((ISNUMBER(MATCH(A1:A8,{"category1","category2"},0))*((B1:B8)+(C1:C8))))

to be used with sample data that you provided:

{"category1", 1, 6;"category1", 2, 7;"category2", 3, 8;"category2", 4, 9;"category3", 5, 10;"category3", 6, 11;"category3", 7, 12;"category4", 8, 13}

The above array-formula computes the value of 40 with this sample data. Can this formula be replaced? The answer in this case is yes.

How?

Assuming that the sample data occupy the range A1:C8,

in E1 enter: =ISNUMBER(MATCH(A1:A8,{"category1","category2"},0))+0 [ copy down this to E8 ]

in F1 enter: =IF(E1,SUM(B1:C1),0) [ copy down this to F8 ]

in G1 enter: =SUM(F1:F8), which also computes the value of 40.

If you have 10000 rows of data, you'll need additional 20001 cells of formulas to compute the same result that a single array formula in a single cell will compute. Admittedly, I didn't time which method will be faster. I leave to others to come up with the comparative numbers.

One caveat though: I'm still not sure whether array-formulas cause Excel to recalculate when you enter or delete something in some cell anywhere in your workbook! If you have named dynamic ranges, Excel is bound to do a recalc to determine whether a dynamic range is changed when you enter something somewhere in your workbook. Excel does this re-determining globally, apparently to guarantee that it didn't miss an update that it must not miss.

I hope it was clear from above that we need to make a distinction between time needed to complete a calculation and the time spent in "extraneous recalcs" that occur because of the factors that I mentioned.

Aladin

First, Dax is correct you can calculate a defined range. I tried as dax said previously, but I neglected to put Calculation to Manual.

I'm compelled to respond to Aladins comments

Array formulas ARE one of the worst offenders for slow recalculation and saving. Arrays do not function in the same way as Excels built in formulas, they actually need to Loop through each and every cell in a given range. This is one of the reasons they cannot reference entire columns. I have seen this from many users that have the problem and the ONLY common link is the heavy use of array formulas.

Dynamic ranges will not cause anywhere near the same problem as arrays as they only use one of the COUNT formulas, with an OFFSET formula. You would need literally hundreds of these to see a noticeable slow down. If fact I would recommend using dynamic ranges in your arrays as this will limit the range the array needs to Loop through.

VBA code will certainly NOT have the slightest effect on recalculation issues. Unless there is loads of code in the Workbook_SheetCalculate. Again I would HIGHLY recommend using VBA in place of formulas. Add-ins are a good example, while they can contains thousands of line of code (often me) they will have very little effect on recalculation.

The biggest problem with array formulas is they become a trap. Once users get the gist of them they tend to use them all too often, as they offer a quick fix. Excel provides database functions and Pivot Tables that are designed to do what most people use array formulas for. As some here no doubt already realize, I object to experienced users recommending arrays to inexperienced or semi experienced users. Especially when they have come looking for help.

Dave
OzGrid Business Applications

You can find a thread at this site specifically on this issue that took place a while back. By the way, array formulas are not the sole reason for recurring recalcs. In my opinion, globally defined named dynamic ranges and a set of volatile functions constitute a signficant cause of recalcs. Another, which seems to escape the attention, is having of loads of VBA code (Macro's etc). FYI, the following are the so-called volatile functions: AREAS
::::::::::::::::::::::::::
::::::::::::::::::::::::::
Big Bob says my reference to Excel says select the range of cells you want to recalculate then use Edit ->Replace ->and then you replace the equals signs at the beginning of formulas with an equals sign .This apparently forces a recalculation of the selected cells
HTH