Remember Me?

#1
May 18th 05, 04:14 AM
 KRAMER Posts: n/a
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 30-60 sec's to complete when you change a cells value. The functions
being used are as follows

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 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 7-4200.

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
May 18th 05, 01:47 PM
 bj Posts: n/a

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 30-60 sec's to complete when you change a cells value. The functions
being used are as follows

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 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 7-4200.

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Marc Bobrow Excel Worksheet Functions 5 May 13th 05 02:39 AM Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM Greg B Excel Discussion (Misc queries) 5 May 11th 05 09:46 PM Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM Krishna Excel Discussion (Misc queries) 6 April 8th 05 02:57 PM

All times are GMT +1. The time now is 09:53 PM.