Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

excel slow calculation (4 processors)

Posted on 2013-10-26
4
36,026 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

861 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