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

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

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

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

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

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

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

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial