Hi all,
I encounter a problem with index or match function perform very slow in large data set
I am using excel to do optimisation for trading.
I am building moving average for price with
the formula i am using does not contain volatile behavior (with my limited knowledge i guess), below are the potential function causing the slow calculation
=AVERAGE(INDEX($F$1:$F$20795,E179-$G$1+1):F179) , got 3 columns each with 20k data using this formula
=MATCH("Cross down",M177:M20871,0), 1 column with 20k data using this formula
=IF(K183=1,INDEX(N184:O20878,N184,2),0), 1 colum with 20k data with this formula
The result of the worksheet will be calculated with changing variable the the final result is copy and paste into another sheet. All this is done by my own VBA code
The speed is really slow when i perform this.
Please help to solve the speed issue
Thank you~~~~~
Attached part of the workbook i am doing here
Remark to note
Initially, i was using Offset and vlookup to handle the result i want, as they are volatile, it is quite slow
Then i replace them with index and match, but it get even slower
Something to mention is when using offset and vlookup to do the job, every core of cpu is running with 100%
after changing to index and match, speed drop and only 12%+- of cpu being utiliesed.
I understand vba is single threaded process , but it is confusing as when i was using offset and vlookup to run the vba, it utiliese every core, then when change to index and match, it is slower and seem like using a core or two
Bookmarks