Solved

excel slow calculation (4 processors)

Posted on 2013-10-26
4
39,050 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
[X]
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
  • 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

695 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