Home 
Search 
Today's Posts 
#1




Calculations too long
Hi Guy's,
I have an inventory list comprising of a goods in and a goods out sheet. I have implemented calculations to show what items are in stock and what are delivered. Both sheets have about 4200 rows of info and the calculations take about 3060 sec's to complete when you change a cells value. The functions being used are as follows Goods Received col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200) col L, =SUMIF('GOODS SENT'!$F$7:$F$4200,F7,'GOODS SENT'!$O$7:$O$4200) col K, =IF(M7+L7=0," ",IF(L7M7,"OVERSUPPLY",IF(M7L7,"IN STOCK",IF(L7=M7,"DELIVERED","NOT DELIVERED")))) Goods Sent col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200) col L, =SUMIF('GOODS RECEIVED'!$F$7:$F$4200,F7,'GOODS RECEIVED'!$O$7:$O$4200) col K, =IF(L7=M7,"COMPLETED",IF(M7L7,"OVERSUPPLY",IF(M7< L7,"INCOMPLETE"))) N.B. Column F is an invoice number and column O is just a value of 1. These formulas are repeated for each cell from row 74200. I have similar formulas in another workbook but it calculates almost immediately. I don't want to do a manual calc.! Would macros be quicker??  KRAMER 
#2




It sounds as though it is doing a full cell all recalc whenever you change a
cell value. this can be caused because of the number of dependencies (ipossible relationships between cells because of equations, etc. I do not know how to determine how many there are.) being over 65,536 in one workbook and under in the other workbook. When the number of dependencies is low just those cells affected by the cell being changed is recalced. when it is high every cell is recalced. a macro which would do the calculations and paste values in KLM for changes in J in just one row might do the job. You may have other dependencies you could get rid of if you have calcs which were done once only but the equations have been left in place. Note: when you try to reduce the dependencies, You will have to close the file, exit excel and restart excel before you can see whether the dlependency tree is reduced enough for faster recalcs. Excel apparently triggers a flag when it sees too many dependencies but the flag is not automatically removed. "KRAMER" wrote: Hi Guy's, I have an inventory list comprising of a goods in and a goods out sheet. I have implemented calculations to show what items are in stock and what are delivered. Both sheets have about 4200 rows of info and the calculations take about 3060 sec's to complete when you change a cells value. The functions being used are as follows Goods Received col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200) col L, =SUMIF('GOODS SENT'!$F$7:$F$4200,F7,'GOODS SENT'!$O$7:$O$4200) col K, =IF(M7+L7=0," ",IF(L7M7,"OVERSUPPLY",IF(M7L7,"IN STOCK",IF(L7=M7,"DELIVERED","NOT DELIVERED")))) Goods Sent col M, =SUMIF($F$7:$F$4200,F7,$O$7:$O$4200) col L, =SUMIF('GOODS RECEIVED'!$F$7:$F$4200,F7,'GOODS RECEIVED'!$O$7:$O$4200) col K, =IF(L7=M7,"COMPLETED",IF(M7L7,"OVERSUPPLY",IF(M7< L7,"INCOMPLETE"))) N.B. Column F is an invoice number and column O is just a value of 1. These formulas are repeated for each cell from row 74200. I have similar formulas in another workbook but it calculates almost immediately. I don't want to do a manual calc.! Would macros be quicker??  KRAMER 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
macro to run series of reports to PDF  Excel Worksheet Functions  
Automatically up date time in a cell  Excel Discussion (Misc queries)  
Files in a directory?  Excel Discussion (Misc queries)  
clock  Excel Worksheet Functions  
time interval calculations in excel  Excel Discussion (Misc queries) 