Question about BigNum in Offset/Average Formula

poorwallace

Active Member
Alright...

I've been playing around with using BigNum and I came across this formula that Aladin posted for someone looking for the average of the last 5 values in a column.

=OFFSET(\$A\$1,MATCH(BigNum,A:A)-5,0,5,1)

It works fine. But if I change A\$1\$ to A\$3\$ it only takes the average of the last 3 values. I don't understand why this is. Other than that, I understand the formula and think it's quite ingenious...

Any help would be appreciated.

Thanks
Wally

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
poorwallace said:
Alright...

I've been playing around with using BigNum and I came across this formula that Aladin posted for someone looking for the average of the last 5 values in a column.

=OFFSET(\$A\$1,MATCH(BigNum,A:A)-5,0,5,1)

It works fine. But if I change A\$1\$ to A\$3\$ it only takes the average of the last 3 values. I don't understand why this is. Other than that, I understand the formula and think it's quite ingenious...

Any help would be appreciated.

Thanks
Wally

This specifies a range: 5 cells after the Nth cell from A1 on including A1 itself. N is given by the MATCH bit: MATCH(BigNum,A:A)-5. When you replace A1 with A3, it will still consider 5 cells after A3 plus N cells including A3.

You are perhaps looking for:

OFFSET(\$A\$3,MATCH(BigNum,A:A)-5-(SUM(ROW(\$A\$3))-1),0,5,1)

I guess my actual question is why this formula

=OFFSET(\$A\$3,MATCH(BigNum,A:A)-5,0,5,1)

Doesn't give the 5th number up from the bottom of the list. The reason I'm confused might lie in my understanding of how the MATCH(BigNum, A:A)
formula works. I thought that the MATCH(BigNum) would give the last entry in the column and the -5 would tell it to reference -5 rows up. So I'm confused as to why the Reference in OFFSET would make a difference.

I appreciate you taking the time to explain this.

Also, your formula incorporating SUM gave the desired result.

And anyone reading, all these formulas are confirmed with Cntrl + Shift + Enter

Hi,

Check out syntax for OFFSET to get it right.

OFFSET(reference,rows,cols,height,width)

or try:

=INDEX(A:A,MATCH(BigNum,A:A)-4)

poorwallace said:

I guess my actual question is why this formula

=OFFSET(\$A\$3,MATCH(BigNum,A:A)-5,0,5,1)

Doesn't give the 5th number up from the bottom of the list. The reason I'm confused might lie in my understanding of how the MATCH(BigNum, A:A)
formula works. I thought that the MATCH(BigNum) would give the last entry in the column and the -5 would tell it to reference -5 rows up. So I'm confused as to why the Reference in OFFSET would make a difference.

I appreciate you taking the time to explain this.

Also, your formula incorporating SUM gave the desired result.

And anyone reading, all these formulas are confirmed with Cntrl + Shift + Enter

No, you don't need to apply control+shift+enter (CSE). If you feed the OFFSET bit to SUM for example, you'll notice that CSE is not required. If you want to see which figures the OFFSET bit captures, select it on the Formula Bar and hit F9. Perhaps what you see with F9 would clarify what I attempted to say with: "5 cells after the Nth cell from A1 on including A1 itself. N is given by the MATCH bit: MATCH(BigNum,A:A)-5. When you replace A1 with A3, it will still consider 5 cells after A3 plus N cells including A3."

MATCH(BigNum A:A) returns the positition/native row number of the last numerical value (not the value itself).

That did clear it up. I had thought that something like that was occuring, but I was having trouble visualizing how it was happening. Also, you're correct that not all the formulas are CSE. Just this formula was CSE

=OFFSET(\$A\$3,MATCH(BigNum,A:A)-5,0,5,1)

Thanks for your help...

Replies
3
Views
134
Replies
26
Views
1K
Replies
7
Views
502
Replies
1
Views
402
Replies
7
Views
535

1,219,569
Messages
6,149,037
Members
450,852
Latest member
dmotz

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?

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

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