Excel file saves very slowly (calculate 4 threads)

[Bas van den Boomen]

I have an excel file with approximately 75 columns and 25,000 rows, the majority of which contain forumulas. If I change something in my file (for example adding a new column with formula) and then want to save my file, this takes about half an hour. At the bottom of the file it says "(4 threads), calculating: 0%". This percentage then increases very slowly until 100% is reached after half an hour. This is of course very annoying and I wonder how I can reduce the saving time?

The long saving time may be caused by the fact that a number of columns contain the following complex formula: = IF (OR (ISNUMBER(SEARCH({"11-jewel"; "12-jewel"; "13-jewel"; "14-jewel"; "15-jewel"; "16-jewel"; "17-jewel"; "18-jewel"; "19-jewel"; "20-jewel"}; $ BQ2)); (ISNUMBER(SEARCH( {"11-jewel"; "12-jewel"; "13-jewel"; "14-jewel"; "15-jewel"; "16-jewel"; "17-jewel"; "18-jewel"; " 19-jewel ";" 20-jewel "}; $ BT2)))); 1; 0).
Can this formula be rewritten to a simpler variant? The goal is that the formula displays 1 when one of the search terms in the quotation marks appear either in column BQ OR BT.

I hope someone can help me.

Sincerely,

Bas van den Boomen

[Pete_UK]

Is that a mis-typed ")" after 14-jewel in the first part of the formula?

Do you have those values from 11-jewel to 20-jewel stored in some cells somewhere?

I would suggest you use COUNTIF instead of ISNUMBER(SEARCH

Hope this helps.

Pete

[Bas van den Boomen]

Hi Pete,

Thanks for the quick response. Yes, that was a mis-typed ")", I edited my original post to make it right.
The values are either stored in column BQ or in column BT. I want the formula to display 1 if it is stored in one of thes columns. Is this easier/possible with COUNTIF? And does this reduce my saving time? Because that is the major problem here.

[Richard Buttrey]

Are there values in BQ2 & BT2 other than text containing "-jewel"

If not then try

Please Login or Register  to view this content.

also check the last used cell on each sheet. You may have accidentally copied formulae to the bottom row.

[Bas van den Boomen]

Hi Richard,

Yes there is other text in the cells. Also, the formula I pasted here is just to create one dummy. I have 7 more of these formulas that are similar but contain *-jewel up to 80.

So for one dummy I have formula: =IF(OR(ISNUMBER(SEARCH({"11-jewel";"12-jewel";"13-jewel";"14-jewel";"15-jewel";"16-jewel";"17-jewel";"18-jewel";"19-jewel";"20-jewel"};$BQ2));(ISNUMBER(SEARCH({"11-jewel";"12-jewel";"13-jewel";"14-jewel";"15-jewel";"16-jewel";"17-jewel";"18-jewel";"19-jewel";"20-jewel"};$BT2))));1;0)
And for the next I have formula: =IF(OR(ISNUMBER(SEARCH.SPEC({"21-jewel";"22-jewel";"23-jewel";"24-jewel";"25-jewel";"26-jewel";"27-jewel";"28-jewel";"29-jewel";"30-jewel"};$BQ2));(ISNUMBER(SEARCH({"21-jewel";"22-jewel";"23-jewel";"24-jewel";"25-jewel";"26-jewel";"27-jewel";"28-jewel";"29-jewel";"30-jewel"};$BT2))));1;0)

And so on until I have 8 dummies till 80-jewel.

So I think your formula won't work the way I want it to work

[Pete_UK]

Put the values from 11-jewel to 20-jewel in some cells (e.g. CA1 to CA10), then use this formula instead of your first one:

=--(COUNTIF(CA$1:CA$10;$BQ2)+COUNTIF(CA$1:CA$10;$BT2)>0)

and copy it down. If you then put the values from 21-jewel to 30-jewel in adjacent cells CB1:CB10 (and so on), then you can just copy the formula across for your other helpers.

Hope this helps.

Pete

[Bas van den Boomen]

Hey Pete,

This formula does not give me the wanted result. It returns only 0's.

[Pete_UK]

In that case It would help if you attached a sample Excel workbook - this only needs to be a shortened version of your real file (i.e. not 25,000 records), but should include enough data so I can test out the formula.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Do not try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

[Bas van den Boomen]

I have followed your steps, hopefully you can find my file.

Additional remark, there is also something going wrong in the first dummy for 0-jewel until 10-jewel (marked red). It displays "1" everywhere because all cells contain 0-jewel, 1-jewel, 2-jewel etc.
For example, it will also display a "1" for 22-jewel, because this is recognized as 2-jewel. Is there a formula which tests for unique text, so 2-jewel but not 22-jewel?

I hope you can help me with this.

[Richard Buttrey]

Hi,

It seems to me you can use the numbers in row 1 and column C to avoid the need for any IFs

So in D2 copied down and across

Please Login or Register  to view this content.

That of course only deals with column C. But if you are happy to use a helper column D and change row 1 to numbers then tge whole thing can be simplified

The Helper column becomes in D2 copied down

Please Login or Register  to view this content.

Then the formulae in E2 copied down and across becomes

Please Login or Register  to view this content.

See attached.

[Bas van den Boomen]

Hi Richard,

Thankyou, this formula works great. However, I'm still stuck with the long (4 threads) calculating time. Especially when I, for example, delete a whole column or row (empty or containing information), it deletes it in just a few seconds, however, saving the file afterwards takes about 20 minutes.

What can I do to speed up the saving process?

[Richard Buttrey]

Are there any conditional formats involved?

Are you able to upload the workbook? Try a .xlsb version since the forum permits up to 9Mb

[Bas van den Boomen]

Since my data is confidential I cannot share the original file with you. I have build a smaller (1000 rows instead of 28000 rows) similar model in which you can find all formulas that I use in my original file. All the variables containing formulas are grouped, so please click on the plus signs to see them. Also, I marked the variables which I think are troublesome, red. If I add or delete columns with formulas like that, it takes a long time to save the file afterwards. Even when I delete the empty column P, when I try to save the file afterwards it takes about half an hour, really annoying.

In my original file I am not able to change my old formulas to the new ones that you provided me with, because it takes hours to save afterwards. Therefore, this file still contains the old formulas.

Although it is a smaller file, I still experience long (4 threads) calculating times.

I hope you can help me speed things up by changing formulas or changing other stuff.

[JeteMc]

In the file attached to post #13, using the Trace Dependents feature (Formulas tab) I get the "...found no formulas that refer the active cell" for columns D:AA. Testing some other cells in some of the other grouped ranges I get the same message. Without understanding the usefulness of the formulas it is harder to know how to update/modify them. For example you might be able to use the following for column BW: =IFERROR(MROUND(VALUE(LEFT(BV2;2));10);"") , depending on how the results are used. Note that I could not find any instances of -jewel in column BS although I realize that may be the case in some of the 28,000 rows of actual data.
Let us know if you have any questions.