Solved

excel slow calculation (4 processors)

Posted on 2013-10-26
4
32,978 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 21

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

My experience with Windows 10 over a one year period and suggestions for smooth operation
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

23 Experts available now in Live!

Get 1:1 Help Now