Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

I have a vary awkward data source from which I need to calculate 888 averages that use a sumproduct formulas making it painfully slow. I would therefore like to at least limit the size of the array but each time the data is updated the number of rows and columns change. It is easy enough to use a formula to calculate the bottom row number and right-most column number but how would I reference those in my sumproduct? E.g. say cell A1 contains the number of rows and c1 contains the number of columns. How would I modify the following formula to change row 2000 to A1 and column LY to cell C1 contents?

=SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*(MasterData!$A$6:$A$2000=SiteId&TEXT($A8,"00"))*MasterData!$F$6:$LY$2000)/SUMPRODUCT((MasterData!$F$1:$LY$1=B$6)*(

=SUMPRODUCT((MasterData!$F

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

```
MasterData!$A$6:$A$2000
```

....becomes....

```
OFFSET(MasterData!$A$6, 0, 0, $A$1)
```

...and...

```
MasterData!$F$6:$LY$2000
```

...becomes...

```
OFFSET(MasterData!$F$6, 0, 0, , $C$1)
```

Note though that the OFFSET function is a volatile function and will recalculate whenever any cell in the worksheet recalculates, whether a precedent cell changes or not. This will have the effect of slowing down the calculation even further.

1- concert your data set to Excel tables and then inside formulas use tables structured references , excel tables has the feature of dynamic range meaning that when you data expands or collapses it will take these expanded and collapsed data into count inside formulas . to convert range to table click anywhere in the table then press control + T , to learn how the structured reference of tables works refer to https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e

2- is using dynamic named ranges , this one you would use if you do not want tables , while using option 1 is recommended. Creating named range using Index and match, Not offset then using the dynamic ranges inside the formula , if you want to use this option let me know, so that I give you example of formula

they do expand the range automatically and even copy the formulas down to added records.

If that is correct, then it is not needed to calculate for all 2000 rows, only the one that match.

Index (without column specification) can get all the values in the matched row, and only the values in that row.

INDEX(MasterData!$F$6:$LY$

The formula for B8 is

=SUMPRODUCT((MasterData!$F

Then the calculation is fast.

24HourMicroDemo.xlsx

## Premium Content

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