Solved

excel slow calculation (4 processors)

Posted on 2013-10-26
4
37,307 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
ID: 39602558
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
ID: 39602690
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 22

Accepted Solution

by:
Ejgil Hedegaard earned 400 total points
ID: 39603225
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
ID: 39603558
All works now. Thanks very much.
CC
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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