First in first out inventory (FIFO) formula very slow calculation

Question

I have a sheet including product transactions from inventory to another and from supplier to another, I import formula for FIFO evaluation from excel sheet to google sheet, but when I populate that formula to all fields sheet get very slow. Below the link for my sheet.

This is One of formulas

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(--IF(MMULT(--(ROW(F$17:F18)>=TRANSPOSE(ROW(F$17:F18))),--IF(E$17:E18=N19,F$17:F18,0))<SUMIF(N$18:N19,N19,O$18:O19),1,0))), 1, 1)

and

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUMPRODUCT(--IF(OFFSET(E$17,,,V19+1)=N19,1,0),OFFSET(F$17,,,V19+1),OFFSET(H$17,,,V19+1))-SUMIF(N$18:N18,N19,Z$18:Z18)), 1, 1)

and

=ARRAY_CONSTRAIN(ARRAYFORMULA((SUMIF(N$18:N19,N19,O$18:O19)-SUMPRODUCT(--IF(OFFSET(E$17,,,V19+1)=N19,1,0),OFFSET(F$17,,,V19+1)))*OFFSET(H$17,V19,,,)), 1, 1)

and finally

=IF(SUMIF(N$18:N19,N19,O$18:O19)>SUM(E$18:E19,N19,F$18:F19),MAX(SUMIF(E$18:E19,N19,G$18:G19)-SUMIF(N$18:N18,N19,P$18:P18),0),Y19)

https://docs.google.com/spreadsheets/d/1xJxCipSh-Q5ltSaGo-kpEPomrZdAI1T8PDH57rc-sOw/edit?usp=sharing

Update....

Formula in column H =IF(F19=0,0,G19/F19) Replaced With =ARRAYFORMULA(IF(LEN(F19:F), IF(F19:F=0, 0, G19:G/F19:F), ))

Formula In Column P =Z19 Replaced with =ARRAYFORMULA(IF(LEN(O19:O), IF(O19:O=0, 0, Z19:Z), ))

Formula In column O =P19/O19 Replaced with =ARRAYFORMULA(IF(LEN(O19:O), IF(O19:O=0, 0, P19:P/O19:O), ))

But still those formula need help with

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(--IF(MMULT(--(ROW(F$17:F18)>=TRANSPOSE(ROW(F$17:F18))),--IF(E$17:E18=N19,F$17:F18,0))

=ARRAY_CONSTRAIN(ARRAYFORMULA(SUMPRODUCT(--IF(OFFSET(E$17,,,V19+1)=N19,1,0),OFFSET(F$17,,,V19+1),OFFSET(H$17,,,V19+1))-SUMIF(N$18:N18,N19,Z$18:Z18)), 1, 1)

=ARRAY_CONSTRAIN(ARRAYFORMULA((SUMIF(N$18:N19,N19,O$18:O19)-SUMPRODUCT(--IF(OFFSET(E$17,,,V19+1)=N19,1,0),OFFSET(F$17,,,V19+1)))*OFFSET(H$17,V19,,,)), 1, 1)

=IF(SUMIF(N$18:N19,N19,O$18:O19)>SUM(E$18:E19,N19,F$18:F19),MAX(SUMIF(E$18:E19,N19,G$18:G19)-SUMIF(N$18:N18,N19,P$18:P18),0),Y19)

Regards

Answer 1

The sheet FIFO has a very long "formula chain" on column H (and may other columns too) from row 19 to row 5395. By formula chain I'm referring to a formula including relative references that was filled down/right so the differences in A1 Notation between a formula an the next are just the relative references but in R1C1 notation the formulas will look the same way.

To improve the performance of your spreadsheet you should reduce the number of formulas. If you don't need so many rows, try deleting the unnecessary rows. If that isn't enough, or you are looking for an optimal performance, replace formula chain by an array formula when this is possible or use Google Apps Script

NOTES

To make an optimal use of you web browser / device / network resources

• avoid the use of open references or enclose them inside ARRAY_CONSTRAIN function to return only the required values.
• on Google Apps Script, avoid or keep the calls to the Spreadsheet Service at the execution time at minimum, specially avoid the to make calls to the Spreadsheet Service on loops, like using a for loop to edit one cell at a time.