Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 18th 05, 04:14 AM
KRAMER
 
Posts: n/a
Default 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

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

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

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to run series of reports to PDF Marc Bobrow Excel Worksheet Functions 5 May 13th 05 02:39 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
Files in a directory? Greg B Excel Discussion (Misc queries) 5 May 11th 05 09:46 PM
clock Wildman Excel Worksheet Functions 2 April 26th 05 10:31 AM
time interval calculations in excel Krishna Excel Discussion (Misc queries) 6 April 8th 05 02:57 PM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017