Alternative to complex OFFSET Function

D

dwestfall

New Member
Joined
May 14, 2018
Messages
5
Hi, I'm currently using an OFFSET function embedded in a SUMPRODUCT formula to calculate a multiple instances of a single production stream. The formula directly underneath "Total Volume" is the following:

SUMPRODUCT($C$5:C5,N(OFFSET($D5:$D$5,ROWS($D5:$D$5)-ROW($D5:$D$5)+CELL("row",$D5:$D$5)-1,0)))

The formula works fine, however, it's repeated thousand of times across 30 other sheets and thus greatly increasing file size and slowing down the spreadsheet to a glacial pace. Does anyone have any recommendations on how to achieve the same result with an INDEX function instead of OFFSET? Thanks!

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Count[/TD]
[TD="align: center"]Volume[/TD]
[TD="align: center"]Instances[/TD]
[TD="align: center"]Volume[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]32[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]66[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]54[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]86[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]71[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]102[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]85[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]115[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]97[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]126[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]107[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]13
[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]136[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]116[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]145[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Count[/td][td]Volume[/td][td]Instances[/td][td]Total Volume[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
1​
[/td][td]
40​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
2​
[/td][td]
32​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
3​
[/td][td]
26​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
4​
[/td][td]
22​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
5​
[/td][td]
20​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
6​
[/td][td]
17​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
7​
[/td][td]
16​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
8​
[/td][td]
14​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
9​
[/td][td]
13​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td]
10​
[/td][td]
12​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td]
11​
[/td][td]
11​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]
12​
[/td][td]
10​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]
13​
[/td][td]
10​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
14​
[/td][td]
9​
[/td][td]
0​
[/td][td]
0​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
15​
[/td][td]
9​
[/td][td]
1​
[/td][td]
0​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet49[/td][/tr][/table]

Your formula returns 0 in every cell? In which cell is your formula entered?
 
Upvote 0
Welcome to the MrExcel board!

As Oscar has alluded to, it is hard to know exactly where your data is located.
However, this formula appears to produce the same results and will be somewhat less burdensome of your system so perhaps you could give it a try.

Excel Workbook
BCD
4VolumeInstancesTotal Volume
540140
632032
726166
822054
920186
1017071
11161102
1214085
13131115
1412097
15111126
16100107
17101136
1890116
1991145
Volume
 
Upvote 0
Oscar / Peter, thanks for the replies. In my spreadsheet, the aforementioned formula is located in D3, with first array in SUMPRODUCT starting in B3 and second dynamic array in C3. So, SUMPRODUCT($B$3:B3,N(OFFSET($C3:$C$3,ROWS($C3:$C$3)-ROW($C3:$C$3)+CELL("row",$C3:$C$3)-1,0))) would be the first formula in D3. Thanks in advance.
 
Upvote 0
dwestfall said:
To clear up any confusion ..
Click to expand...
Actually, for me at least, you have created more confusion.
- You say that the (first) formula is in D3, but in the sample file it appears to be in D4
- If the values in column D of the sample file are the correct results, they seem to follow a very different pattern to the results shown in post #1 above. Can you explain how the first few results (say D4 to D8) would be obtained if doing it manually?

Excel Workbook
ABCD
1Total
2CountVolumeInstancesVolume
3
4140
5232140
63262112
742290
85201114
96172176
10716147
118143244
129131246
131012208
1411111220
1512101235
Sheet1
 
Upvote 0
Apologies for the confusion; the pattern you posted above from my file is correct for what I'm seeking help with. Same with cell location of formula and lookup range's.

To do this manually, you'd create a two way matrix which multiples each "instance" by each volume value over a time series. I've included a second version of the file to demonstrate this visually here:

https://www.dropbox.com/s/55r6vp8mfpjrpop/Array Formula Example 2.xlsx?dl=0
 
Upvote 0
Here's the manual form without having to open file attachment:


Book1
HIJKLM
1Volume4032262220
2Instances12345
3
4140322622
52806452
6
7140
8Total Volume4011290114
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Alternate for volatile function Indirect.
Replies
4
Views
97
Alex Blakenburg
A
K
  • Question Question
Populate Remarks Based on Criteria
Replies
3
Views
79
Sergius
S
B
  • Solved
More Efficient Looping
2
Replies
19
Views
244
bhsoundman
B
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K
S
  • Question Question
Using output of LAMBDA within a function
Replies
3
Views
198
Phuoc
P

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top