Speed up Calcs using INDIRECT function.

Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
I can't help you with your existing formula, but I assume from column E's heading you are calculating STDEV.S. Are you aware that there's a built-in function for that? Here's an article from Microsoft that shows how to use it.
Ian BellretiredAuthor Commented:
Martin, I was using an array which that article alludes and works fine on a single series of variables but when using an indexed range coupled with 100,000+ rows it is not practical. Thanks anyway.
NorieAnalyst Assistant Commented:
Ian

I've not had a chance to look at your workbook properly but I was wondering why you think using INDIRECT will speed things up.

Are you using formulas to determine the ranges to apply other formulas to?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Ian BellretiredAuthor Commented:
I use that sample formula for another purpose and the INDIRECT function speeds it up dramatically
NorieAnalyst Assistant Commented:
Here's a couple of links that suggest using INDIRECT might not actually help in speeding up calculation.

Excel Performance

OEvil Functions
Ian BellretiredAuthor Commented:
Norie I can assure you it does for the purposes I use it for.
I just need someone to add the INDIRECT function to the formula in E column.
NorieAnalyst Assistant Commented:
Ian

Which part of the formula do you want to change to use INDIRECT?
Ian BellretiredAuthor Commented:
not changing, just adding.
Martin LissOlder than dirtCommented:
...when using an indexed range coupled with 100,000+ rows it is not practical.
If that's because you would have to manually add formulas to multiple sections of the column then perhaps I could write code for you that would automate the process of adding the STDEV.S formulas.
Ian BellretiredAuthor Commented:
What are your thoughts on the sample formula in column 'M' of the worksheet ?
Martin LissOlder than dirtCommented:
I assume you are not asking that question of me.

Can you explain why "...it is not practical"?
Ian BellretiredAuthor Commented:
It was not practical because the methodology was meant for non indexed series and certainly not 6 figure rows of data

I was asking you .... are you able to add the INDIRECT as shown in the sample I provided ??
If not no probs.
Martin LissOlder than dirtCommented:
Yes, probably. Please show me what the formula for E2 and E3 should be.
Ian BellretiredAuthor Commented:
I don't understand your question.
I'm asking YOU how the formula in column E should be ?
Please look at the formula I am using in column E and also the SAMPLE ONLY formula in column M
and see if you could use the formulas in columns G:H to help  speed up the calculation process.

As an example the below formula which is the sample formula before speeding modification was added
=IF(WF5="","",SUMPRODUCT((A$5:$A$1000=A5)*(B$5:B$1000>B5))+1)
Then after added it look like this.
IF(ISBLANK(A2),"",IF(AND(A2=A1,K2=K1),L1,SUMPRODUCT((INDIRECT("$A"&G2&":$A"&H2)=A2)*(K2<INDIRECT("$K"&G2&":$K"&H2)))+1))))
I changed the columns to G and H to show how those two columns can be used to assist.

An expert on the forum modified the formula for me a long time ago and speeded up the process immensely.
Please don't ask me questions about the formula as I won't be able to answer them.
If you cannot combine them no problem.
Martin LissOlder than dirtCommented:
I have code that generates the following formula for E2.

=IF(A2="","",IF(J2=0,0,IF(ISBLANK(A2),"",IF(AND(A2=A1,K2=K1),L1,SUMPRODUCT((INDIRECT("$A"&G2&":$A"&H2)=A2)*(K2<INDIRECT("$K"&G2&":$K"&H2)))+1))))

I used the formula in M2 as a template but the result of the new formula in E2 is a #REF! error which may be due to my assumptions which are that in "translating" the formula in M2, column 'J' in column M should be column 'B' in E2, and similarly that 'K' s/b 'C', and 'L' s/b 'D'. Hopefully you or Norie can help me correct the formula.
Ian BellretiredAuthor Commented:
You have used the whole of the sample formula. Remember the sample formula is just that, a sample. The formula that needs modifyiing is column E which means you keep all of the components like SQRT, SUMIF and COUNTIF and only add INDIRECT and ISBLANK if needed
Ignore column range  J:M
Only use columns  A  E  and D  as well as the helper columns  G and H
Ejgil HedegaardCommented:
The problem is that the sumif and countif formulas in column E use all 120,000 rows.
Including rows above, where there will never be a match, since the calculation is only done on the first occurrence of the Index number.
With indirect you still need to find the end row with a countif.
That would calculate faster if it only looked from actual row and perhaps 100 rows down, and not all 120,000.
The number of rows must be greater than the maximum number of repeated values in Index.
But even using 1,000 is much less than 120,000.

But then the formula in column E could do the same without indirect, only calculate 100 rows down.
Then the formula would be
=IF(D2=0,0,IF(A2=A1,E1,SQRT(SUMIF(A2:A101,A2,D2:D101)/(COUNTIF(A2:A101,A2)-1))))
Ian BellretiredAuthor Commented:
I have a total of 20 worksheets each one with number of rows varying between 70,000 and 170,000 and forever expanding.
Each worksheet has 87 column of variables making a total of say 100,000 rows * 87 * 20 = 174 million cells and to be updated every three months.
Seems an impossible task in Excel, I will have to look at other options. One I have in mind is with Stata for which I have a licensed copy.
Martin LissOlder than dirtCommented:
You might also consider Access which is much better than Excel at handling large amounts of data.
Ian BellretiredAuthor Commented:
True, Access can handle large amounts of data however I doubt very much that it can aid in converting values to z scores.
Martin LissOlder than dirtCommented:
I found this article and SELECT STDEV(Bonus) FROM SalesPerson; among others on the web.
Ian BellretiredAuthor Commented:
Based on that article are you able to offer a solution on how it can be done ?
Martin LissOlder than dirtCommented:
No but I'd bet the information is out there, or ask another question here that includes the Access topic. In my administrative capacity I've added Access to this question so perhaps someone will respond that can answer you last question, because I can't.
Ian BellretiredAuthor Commented:
Ejgil,  If you refer to the sample data column 'M' you will see that it uses the 2 helper columns G and H Start and End. That is the idea behind the speeding up process. It only uses max number of rows between each series. I want to add those columns G:H to the formula in column 'E' and the way to do it if I'm not mistaken is by adding the INDIRECT function. But if you have an alternative way to add those columns then I'm all ears.
Ian BellretiredAuthor Commented:
Thanks for all your help Martin it's keeping the thread alive if nothing else.
Ejgil HedegaardCommented:
Using Start/End columns will help since they can be used for 3 calculations.
AVERAGE, column C
=AVERAGEIF(INDIRECT("$A$"&$G2&":$A$"&$H2),$A2,INDIRECT("$B$"&$G2&":$B$"&$H2))

Open in new window

Helper, column D
=POWER(B2-AVERAGEIF(INDIRECT("$A$"&$G2&":$A$"&$H2),A2,INDIRECT("$B$"&$G2&":$B$"&$H2)), 2)

Open in new window

STDEV.S, column E
=IF(D2=0,0,IF(A2=A1,E1,SQRT(SUMIF(INDIRECT("$A$"&$G2&":$A$"&$H2),A2,INDIRECT("$D$"&$G2&":$D$"&$H2))/(COUNTIF(INDIRECT("$A$"&$G2&":$A$"&$H2),A2)-1))))

Open in new window

And the formula for calculating End changed to
=IF(A2="","",IF(A2=A1,H1,G2+COUNTIF($A2:$A$26,A2)-1))

Open in new window

Only calculating from actual cell down.
Indirect-Function.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ian BellretiredAuthor Commented:
Ejgil, perfect, thanks a million. It took 2 seconds for 116,000 rows on my blistering fast computer with an i9 processor and 64 gb ram.
That INDIRECT function is really powerful. You have nailed it. I should use it for my other formulas that I use.
Thanks again
Ian
Thanks also to Norie and Martin for your contributions.
Ian BellretiredAuthor Commented:
Ejgil, I have another formula I would like the INDIRECT condition added to help speed up. It is an array. should I open a new question ?
Ejgil HedegaardCommented:
The functions could be more simple.
Since the range is set with the columns G and H, there is no need for if in the formulas.
H2
=IF(A2="","",IF(A2=A3,H3,ROW()))

Open in new window

E2
=IF(D2=0,0,IF(A2=A1,E1,SQRT(SUM(INDIRECT("$D$"&$G2&":$D$"&$H2))/(H2-G2))))

Open in new window

D2
=POWER(B2-C2, 2)

Open in new window

C2
=IF(A2=A1,C1,SUM(INDIRECT("$B$"&$G2&":$B$"&$H2))/(H2-G2+1))

Open in new window

Ejgil HedegaardCommented:
Yes, open a new question.
You could make a reference here.
Ian BellretiredAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.