Solved

excel slow calculation (4 processors)

Posted on 2013-10-26
4
30,988 Views
Last Modified: 2013-10-27
Hello,

I am trying to build up a workbook and have set up a formula that seems to overload the excel calculation capacity.

the formula is:

=LOOKUP(1,1/(INDIRECT("'"&B2&"'!$H:$H")=A2),INDIRECT("'"&B2&"'!$X:$X"))
=LOOKUP(1,1/(INDIRECT("'"&B2&"'!$H:$H")=A2),INDIRECT("'"&B2&"'!$AA:$AA"))

The cell B2 corresponds to a worksheet in the workbook with the corresponding name. The cell A2 is a reference which the formula uses to find the last row in the worksheet with that reference in column H and then the  result is the cell value in column X of that row.

The workbook will have about 20 worksheets with 10 columns and 4000 rows.

Is there a better way to write the formula or create a function in VBA to speed up the calculation process?

Thanks,
CC
0
Comment
Question by:CC10
  • 2
4 Comments
 
LVL 14

Expert Comment

by:Juan Ocasio
Comment Utility
What exactly are you trying to accomplish?  There would be a better way using vba, using For Each and cycling through each worksheet.

Perhaps you can post an example with your requirements.
0
 

Author Comment

by:CC10
Comment Utility
I am trying to create a summary sheet showing various statistics of financial trades in the currency market. If you look at the EURUSD worksheet, you can see that in row 40, a position was opened (the sale of EUR 600,000 against the dollar), there was an addition (further sale of 300,000), row 170,  and then the position was closed. Row 193. Columns M to AA calculate various statistics.
Each Trade or Transaction has a reference number. Column A in the Summary worksheet and column H in the other sheets.
The summary sheet will show the relevant statistics for the transaction as well as the actual trades. In other words, the complete history.
Since I have about 20 currency pairs and about a total of 160 transactions, I am trying to create a summary sheet that will show all the details once I have inputted all the information. Since I can update the sheets through a DDE link, the intention is that the summary sheet will show the latest open transactions on a daily basis as well.

I hope this all makes sense.

CC

The workbook may become large but I can copy out the formulas of each worksheet. I only really need the formulas in the last one or two rows.
NEW4HOUR.xlsx
0
 
LVL 21

Accepted Solution

by:
Ejgil Hedegaard earned 400 total points
Comment Utility
The Lookup functions are searching for the last occurrence of the search value.
Looking at the entire column, means it has to search all 1 million rows.
Doing that 20 times, takes some time.

It will be much quicker using the Index function, and only search for the row once.
Index is extremely fast, since it points directly at the cell, without search.
But it requires a row number, and Lookup don't provide that.

Here is my suggestion.
Set Excel calculation to manual before loading the worksheet.
Then remove all the Lookup functions on Summary, otherwise it will be impossible to change anything.
Then switch to Automatic calculation.

If column H is changed like this (formula for H3), H2 is blank don' see the reason why.
=IF(AND(I3=0,I2=0),0,IF(AND(I3<>0,I2=0),MAX(H$1:H2)+1,IF(AND(I3=0,I2<>0),-H2,H2)))
Copy down (and/or to H2 as well).
The different transaction numbers are identified by column I (there are 29 in EURUSD), setting a number 1 greater that the previous max, and setting the last record for each transaction number the negative value for the transaction number.
Then this negative number (-1, -2 et,) can be searched by the match function.
For test inserted in A3 on Summary.
=MATCH(-A2,INDIRECT("'"&B2&"'!$H:$H"),0)
The function searches the column (indirectly addressed) like the lookup function, but stops when the value is found, row 193 for transaction 1.
Then the values in D2-H2 and C8-E8 can be found using Index.
For D2 (value in column X) is
=INDEX(INDIRECT("'"&$B$2&"'!$x:$x"),$A$3,1)
Copy to the other cells, and change the column reference x to aa, p etc.
When I change A2 to 29 (last transaction end, at row 3660) calculation take just a blink.
0
 

Author Closing Comment

by:CC10
Comment Utility
All works now. Thanks very much.
CC
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now