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.

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

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

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.

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?

Excel Performance

OEvil Functions

I just need someone to add the INDIRECT function to the formula in E column.

...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.

I was asking you .... are you able to add the INDIRECT as shown in the sample I provided ??

If not no probs.

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((

Then after added it look like this.

IF(ISBLANK(A2),"",IF(AND(A

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.

=IF(A2="","",IF(J2=0,0,IF(

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.

Ignore column range J:M

Only use columns A E and D as well as the helper columns G and H

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,SQR

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.

I found this article and** SELECT STDEV(Bonus) FROM SalesPerson;** among others on the web.

AVERAGE, column C

```
=AVERAGEIF(INDIRECT("$A$"&$G2&":$A$"&$H2),$A2,INDIRECT("$B$"&$G2&":$B$"&$H2))
```

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

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))))
```

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

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 trialThat 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.

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()))
```

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

D2```
=POWER(B2-C2, 2)
```

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

Microsoft Office

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.