Question about BigNum in Offset/Average Formula

[poorwallace]

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

 

[Aladin Akyurek]

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)

 

[poorwallace]

Aladin,

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

 

[fairwinds]

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)

 

[Aladin Akyurek]

Aladin,

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

 

[poorwallace]

Aladin,

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