Determine cause of prolonged “Calculating Formula…” notification
Determine cause of prolonged “Calculating Formula…” notification
I recently took care of a working data sheet, with about 30 sheets of several thousand rows each sheet, with many cell colored for difference purpose. Most of the sheets only have raw data; a few working sheets have formulas / arrayformulas.
Here's a portion of my workbook:
In the red circle, you can see "Calculating formula....". This appears in my sheet very frequently, generally whenever I edit anything in any cell in any sheet.
I followed https://www.benlcollins.com/spreadsheets/slow-google-sheets/ which doesn't help much to my situation. yeah, all 27 of them.
https://www.benlcollins.com/spreadsheets/slow-google-sheets/
Now what I want to know is, is there any way to know just what is Google Sheets doing during the time that "Calculating formula..... " bar appear ? So I can see whichever function / formula is taking my precious time and then improve it.
Edit --
Since I cannot share the sheet, at least I'll share the working formula so that you guys can figure what is the sheets doing.
These are to be pasted to get the input data when working. Result will then be value pasted again to data sheet. These formulas are only in the working sheet:
v
=VLOOKUP($D2,TEMP!$A$1:$AT$99,23,FALSE)
=VLOOKUP($D2,TEMP!$A$1:$AT$99,11,FALSE)
=VLOOKUP($D2,TEMP!$A$1:$AT$99,16,FALSE)
=REGEXREPLACE(TO_TEXT(VLOOKUP($D2,TEMP!$A$1:$AT$99,17,FALSE)), "^84", "0")
=VLOOKUP($D2,TEMP!$A$1:$AT$99,46,FALSE)
=VLOOKUP($D2,TEMP!$A$1:$AT$992,18,FALSE)
=CONCATENATE(VLOOKUP($D3,TEMP!$G$1:$AS$492,9,FALSE), ",", VLOOKUP($D3,TEMP!$G$1:$AS$492,10,FALSE), ",", VLOOKUP($D3,TEMP!$G$1:$AS$492,7,FALSE))
=VLOOKUP($D3,TEMP!$G$1:$AS$492,6,FALSE)
=REGEXREPLACE(TO_TEXT(VLOOKUP($D3,TEMP!$G$1:$AS$492,12,FALSE)), "^84", "")
This formula is used to set the status of the data -- only in working sheet:
v
ARRAYFORMULA(IF(ISERROR(MATCH(E7:E10178,DonHangTraVe!A1:A20014,0)),IF(ISERROR(MATCH(D7:D10178,DonHangTraVe!C1:C20014,0)),IF(ISERROR(MATCH(D7:D10178,LAZ_Delivered!B1:B20000,0)),"","Received"),"Returned"), "Lost")))
This formula is placed in all of the data sheets. It will get the value in column E, turn them into links to their corresponding data in the server:
v
=ARRAYFORMULA(IF(ROW(F:F)=1,"LINK", IF(LEN(E:E)=13, HYPERLINK("http://www._____________________?key=" & E:E, E:E), "")))
Those are the main formulas.
There is this one formula in a sheet, but it is turned off by placing a value one cell below. (Not me. I'm still trying to figure this out. Even the sheet maker unsure why he did that)
=ArrayFormula(trim(concatenate(filter(indirect("AQ1:AQ500")&" "®EXREPLACE(indirect("AR1:AR500"), "Dòng sản phẩm tương thích:.+",""), indirect("G1:G500")=G1)&CHAR(10))))
I turned the last formula down by setting an if with a switch (Benlcollins idea) but it doesn't help much.
if
After looking at all the formula, I think maybe the problem of my sheet lie in the arrayformula which adds the link.. I will tinker with it for now.
Still, the question remain.. is there any tool / script / addon that shows what google spreadsheet has been doing under these "Calculating formula..." bar ??
On the side note, this sheet is not using any script, in case you wonder if there's anything in onEdit(e) ..
Use of
INDIRECT can often cause excessive recalculation as well. Consider sharing some examples of the actual formulas in use.– tehhowch
Sep 12 '18 at 13:02
INDIRECT
IMO, BenCollins is plain wrong. And I suggest that you use ARRAYFORMULAS for performance. Consider changing all dumb INDEX/MATCH formulas to ARRAYFORMULAS/VLOOKUP() or QUERY(). Delete all empty rows. If possible, Avoid all conditional formatting, unless essential.
– TheMaster
Sep 12 '18 at 13:02
@ttarchala no, there is no sum. This sheet is not about finance or anything related to number. Plain data to be stored to manage work in our shop. Therefore no sum, no count, if that matter.
– HaPhan
Sep 13 '18 at 8:05
@I'-'I yeah, I agree with you on the arrayformula part. I have experienced myself how quick the arrayformula with a sumif work in 500 rows in compare with 500 individual sumif(s). Well, among the methods, i find that #4 - using the Developer Tool looks interesting enough. I'm currently messing with it, I hope I can figure something out..
– HaPhan
Sep 13 '18 at 8:20
0
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
I don't see sumproduct() calls on the list you linked. Have you checked for that? In my experience they are quite frequently a source of slow spreadsheets, in which case they can be replaced by faster mmult(). Other than that, I don't know of any general method of debugging GSheet performance.
– ttarchala
Sep 12 '18 at 10:01