LinkBack URL
About LinkBacks
I've just reduced my workbook by over 50% and removed all conditional formatting but it is still very slow. Entering data in one of three cell brings up "Calculating 4 threads". Is there anyway of discovering which four threads its looking at?
I've just reduced my workbook by over 50% and removed all conditional formatting but it is still very slow. Entering data in one of three cell brings up "Calculating 4 threads". Is there anyway of discovering which four threads its looking at?
Last edited by Marvo; 01-01-2021 at 05:29 AM.
Do any of the formulae use full column references? Do any use volatile functions such as OFFSET and INDIRECT?
I think the message simply means that all four cores are being used to process the data, but I know no way of determining which formulae are causing that.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Okay. I got rid of the offset I could find and Glenn helped me with limiting full column references. It never used to be slow and it only seems to be these three cells where the problem lie, others parts of the workbook update instantly. I spent the morning reducing the size in the hope that would help, got rid of a lot of formula but to no avail. Oh well, I guess I'll have to put up with it. I suppose I could move to manual calculation rather than automatic. At least then the three cells should calculate together. Thanks anyway.
OK - so tell us what is in those three cells, please. If it's manually entered data, then it's not those cells: it's formulae feeding off of them.
It's the latter. I need to discover what feeding off them and whether I need that to happen. If I switch to manual it takes about the same time to do all three as it does on each automatically.
One odd thing. When you place something in a cell and press enter, it doesn't complete until the calculation is done though it doesn't show its doing a calculation. However if you place something in the cell, then click on another cell it then shows you the calculation taking place. I don't know whether that means anything?
I wish I could remember when the change first happened, that might tell me what slowed the workbook down. I did have some VBA put in but the chap told me it couldn't be to do with that.
I have found a whole bunch of OFFSET and they all refer to the three columns in question, J,K & L on the All page. The OFFSET is on the managers page in the red box. Could I use a different formula to achieve the same results?
I'm not sure this will speed up the workbook but worth a try.
As an aside, to reduce the workbook to put on here I cleared a lot of data and it hardly reduced at all. I then cleared a large block than had hardly any data in it and it reduced substantially. Doesn't make much sense?
Those will definitely slow things up. To streamline it, the name and years should best be in different cells. can I separate them?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Just to add to my last paragraph there. Earlier today I reduced a block of 20 columns which had 324,240 cells with formula to less than 1,000. That reduced my workbook from just under 4000KB to 1872KB. I just experimented and deleted the 20 columns with just the 1000 cells left with formula and the workbook reduced in size to 713KB. Anybody give me an explanation for that?
The formula was giving you incorrect results, in anuy event.
I'll just leave the names the way they are. it'll make little difference.
Nope - not without knowing EXACTLY what you deleted.
Please answer Glenn's question above.
Sure, its there tenure. That can be in a separate column.Originally Posted by Glenn Kennedy
That's frightening.
Here you go. No idea if it's qucker, bu the OFFSETS are gone.
I checked a few before/afters and they were miles out.
I am a great believer in double checking. For example, in an situation like this, I'd always add up the column total and check it against total number of wins, etc, and have the two values (calculated from the table and from the raw data) in adjacent cells to act as a visual stimulus.
Thanks Glenn, I'll transfer the formula to my workbook and see whether it helped with speed. I'll get back to you.
Don't do anything yet!!!
There are loads of errors on that page.
There are quite a few managers who do not appear in that red table.
Why?
I think a misunderstanding of what the table is doing.
OK.
1. There is a spelling mistake in AJ23.
2. How do you want me to distinguish between D Bowens 2 periods at the top. There is no column in ALL exactly corresponding to NAME (period in charge)
The OFFSETS are gone. However, I still need an answer wrt Mr Bowens, before this is complete.
The other formula referred to each managers first game in charge, then worked down from that point. As Dave Bowen managed the team twice he had two starting points. That is maybe is why OFFSET was used in the first place? As we are now using MATCH I guess I'll need to refer to Dave Bowen as maybe 1 & 2. I'll have to have a think about it.
Possibly so... I had nothing to do with that. I avoid OFFSET, INDIRECT, etc, if at all possible.
It's a tricky one as another table refers to Dave Bowen in his entirety, so counts all his games from both spells. I'll come up with something. Thanks for the help Glenn, it will be interesting to see if the workbook quickens up.
Okay, the solution I came up with was to change all the formula in the table to yours Glenn EXCEPT the second Dave Bowen which I've left as the one OFFSET formula in the workbook. It's the best I could come up with. However after all that work, it has not speeded up the workbook one iota. I'm going to have to do some more experimentation to try and discover what is slowing it down. I think the answer lies in my post at 3:17pm.
No, its not that. Deleted those columns (which reduced the entire workbook to just 713KB) but still hasn't had any affect on the speed of the calculations. I'm at a loss now.
You have a lot of stuff going on. So as you add more stuff, it will get slower. The only other suggestion (you won't like it...) is that every time you add a row, the whole thing recalculates. The formulae in your search tables could be modified to =IF(searchterm="","",formula) and the search term cell left BLANK.
This would stop them calculating every time a new row is added. They would only calculate when a search term was selected. This may help, but whether it would help enough....
Anyway, I have had enough for today. See you next year.
G.
Happy New Year. Have a good one.
Well I got changed every formula that looked at entire rows and still the workbook is slow. I think I've give up.
So many variations within the thread that it is difficult to know what you are currently working with.
One thing I see in both your file (post #6) and Glen's file (post #21) is multiple copies of the exact same slow linear "exact match" lookup. In the post #6 file, I see this in managers columns X:AF. In Glen's file, I see it in the INDEX(...MATCH(...)) formulas that define the counting/summing ranges of managers AL21:AP35. Exact match lookups are notoriously slow even though they are quite common. I would recommend seeking ways to improve your lookup/search strategy:
1) Can you sort any of your lookup tables? Binary "approximate match" lookups are much faster.
2) Can you move the "lookup" MATCH() part of your function into a helper cell? Then your spreadsheet will only need to perform the lookup step once, and have multiple INDEX() functions that reference the one lookup function (example here and someone put it in a spreadsheet in post 13: https://www.excelforum.com/excel-for...ml#post4041181 ).
Hi All, Well I'm going to mark this as solved because I've carried on "tidying things up" as at some point everything has got back to normal, instant calculations. Unfortunately i have no idea which was the fateful move that did it so this is not going to be much help to anybody. I will say though it "felt" to me more like a conflict problem more than just sheer amount of formula, Excel was struggling with something, I just didn't know what. Anyway thanks for everybody who tried to help and MrShorty, I didn't receive notification of your post so have only just seen it and despite the problem being "solved" will look into what you say.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks