Fast formulas once second, slow formulas the next

[NAMIV]

I have a file where one second the entire workbook only takes a couple seconds to calculate everything, but every now and then it will suddenly take an unreal amount of time to process, and will continue doing this.

I have played around with this and it will happen without any changes to the workbook; I will hit f9 and it will be fine, I will save it and hit f9 again and all of a sudden the problem occurs. When it starts happening I can go to each individual sheet, hit calculate sheet and it only takes a second for each sheet, but when I try to calculate the whole workbook at once either with Automatic Calculation or with f9, it takes forever.

The problem has been solved by deleting one of two items, 1) specific formula, mainly with sumproducts (works every time) 2) a set of form control buttons and an offset formula based on the buttons linked cell (works sometimes). I would like to keep both of these features of the workbook while still finding a solution.

 

[DILIPandey]

Hi NAMIV,

One suggestions - check if your formulas has specific range reference or full column / row ranges references ? for example, there is a difference between A:A and A1:A10. Thanks.

Regards,
DILIPandey

 

[NAMIV]

Hi NAMIV,

One suggestions - check if your formulas has specific range reference or full column / row ranges references ? for example, there is a difference between A:A and A1:A10. Thanks.

Regards,
DILIPandey

Thanks DILIPandey, - I had changed the formulas from full columns to a limited range previously as I wondered that myself, but no luck. The formula itself is below, I just changed the sheet name that is referencing another tab to Sheet2 for confidentiality. It's pretty cumbersome and I would understand why it might cause issues, the odd thing is it works one second and then doesn't the next.

=IF(C$3<=$B$2,SUMPRODUCT(--('Sheet2'!$A$2:$A$50401=C$4),'Sheet2'!$F2:$F50401,'Sheet2'!$G2:$G50401,OFFSET('Sheet2'!$G2:$G50401,0,MATCH($B$2,'Sheet2'!$1:$1,0)-MATCH('Sheet2'!$G$1,'Sheet2'!$1:$1,0)))/SUMPRODUCT(--('Sheet2'!$A2:$A50401=C$4),'Sheet2'!$F2:$F50401,'Sheet2'!$G2:$G50401),0)

 

[NAMIV]

Thanks DILIPandey, - I had changed the formulas from full columns to a limited range previously as I wondered that myself, but no luck. The formula itself is below, I just changed the sheet name that is referencing another tab to Sheet2 for confidentiality. It's pretty cumbersome and I would understand why it might cause issues, the odd thing is it works one second and then doesn't the next.

=IF(C$3<=$B$2,SUMPRODUCT(--('Sheet2'!$A$2:$A$50401=C$4),'Sheet2'!$F2:$F50401,'Sheet2'!$G2:$G50401,OFFSET('Sheet2'!$G2:$G50401,0,MATCH($B$2,'Sheet2'!$1:$1,0)-MATCH('Sheet2'!$G$1,'Sheet2'!$1:$1,0)))/SUMPRODUCT(--('Sheet2'!$A2:$A50401=C$4),'Sheet2'!$F2:$F50401,'Sheet2'!$G2:$G50401),0)

I noticed the $1:$1 and changed it to a limited range, still happening though.

 

[NAMIV]

I noticed the $1:$1 and changed it to a limited range, still happening though.

Another update... turns outs deleting the formula doesn't work every time...

 

[jkpieterse]

How much time does it take to do control+alt+shift+F9? ANywhere close to the "unreal amount of time"?

 

[NAMIV]

How much time does it take to do control+alt+shift+F9? ANywhere close to the "unreal amount of time"?

I have tried F9, Shift+F9, Ctrl+Alt+F9, Ctrl+Alt+Shift+F9. I'm also not sure how calculating everything including dependent formulas, non-changed cells, etc. would cause a quicker calculation? Can you explain?

 

[jkpieterse]

I wasn't implying the calculation would become quicker, I was just wondering what made it slow once in a while and suspected that is when a full recalc occurs for some reason. control+alt+shift+F9 is a full recalc and rebuild of the calc tree, which is the slowest possible calc.