Average Offset

J

JimS

tom 85
tom 88
tom 93
bob 66
ed 33
tom 101
tom 232
ed 44
bob 99
bob 87
ed 33
ed 44
bob 72

Starting in column A10 are the names and in column B10 are the
amounts.

In a table I have this formula:

=AVERAGE(OFFSET(B10,COUNT(B:B)-3,0,3,1))

This gives me the average of the last 3 amounts for the entire column,
regardless of names. I need to come up with a formula that gives me
the average of the last three amounts for just Bob (or ed or whoever).
When I get one formula, I can then make the others.

Thanks
 
T

T. Valko

the average of the last three amounts for just Bob (or ed or whoever).

Will there *always* be at least 3 entries for Bob? If not, what should
happen?
 
B

Bernd P

Hello Jim,

I dont like it too much but this might be something to start with:
=(INDEX(B1:B995,LARGE((A1:A13="Bob")*ROW(A1:A13),1))+INDEX
(B1:B995,LARGE((A1:A13="Bob")*ROW(A1:A13),2))+INDEX(B1:B995,LARGE
((A1:A13="Bob")*ROW(A1:A13),3)))/(LARGE(--(A1:A13="Bob"),1)+LARGE(--
(A1:A13="Bob"),2)+LARGE(--(A1:A13="Bob"),3))

Regards,
Bernd
 
C

Claus Busch

Hello Jim,

Am Fri, 30 Oct 2009 11:38:07 -0700 schrieb JimS:
=AVERAGE(OFFSET(B10,COUNT(B:B)-3,0,3,1))

This gives me the average of the last 3 amounts for the entire column,
regardless of names. I need to come up with a formula that gives me
the average of the last three amounts for just Bob (or ed or whoever).
When I get one formula, I can then make the others.
Click to expand...

I hope, this will help:
http://www.claus-busch.de/Excel/Average.xls


Regards
Claus Busch
 
J

JimS

Ummm, the way I have it set up I won't start using the data until I
have at least three entries for each name. With less than three
entries they don't qualify.

Later on, I'll be averaging for 6 and 9 entries as well...as soon as I
get enough data.

As to what should happen? Not sure. Whatever works best. N/A,
nothing, false, blank cell, doesn't really matter.

Thanks
 
J

JimS

Hi, Claus. Thanks a lot for that! I'll play with it. Looks good.
Very cool of you to do that.

(I would never have been able to figure that out on my own.)

Thanks again.
 
J

JimS

Hello Jim,

I dont like it too much but this might be something to start with:
=(INDEX(B1:B995,LARGE((A1:A13="Bob")*ROW(A1:A13),1))+INDEX
(B1:B995,LARGE((A1:A13="Bob")*ROW(A1:A13),2))+INDEX(B1:B995,LARGE
((A1:A13="Bob")*ROW(A1:A13),3)))/(LARGE(--(A1:A13="Bob"),1)+LARGE(--
(A1:A13="Bob"),2)+LARGE(--(A1:A13="Bob"),3))

Regards,
Bernd
Click to expand...

Thanks, Bernd. I'll try it out.
 
J

JimS

Claus, I have a question. In cell f10,g10 and h10 you appear to have
the exact same formula, yet it gives three different averages. What
am I missing? How are you doing that?

Thanks
 
J

John

Hi Jim
He's using the function ">=COLUMN()-3" to count.
Your first result is in column (6)-3 then column (7)-3 and so on.
Move the formula around and you will see the result for more or less average
depending where you move it.
Very nice formula.
HTH
John
 
T

T. Valko

Try this array formula** :

=IF(COUNTIF(A10:A22,D10)<3,"",AVERAGE(IF(ROW(A10:A22)>=LARGE(IF(A10:A22=D10,ROW(A10:A22)),3),IF(A10:A22=D10,B10:B22))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Where D10 = the name of the person to get the average for.

If there are less than 3 instances of "name" the formula will return a
blank.
 
B

Bernd P

Hello Jim,

I suggest not to dig deeper into Claus' formula. It is location-
dependent and that is not a wise thing to do (insert a new column
between columns E and F and you will see).

Have a look into
www.sulprobil.com/software/20091031_PB_01_Average_Last_X.xlsm

You can find a pivot table solution for the average of last three
elements. But since you mentioned that you only want to show the
average of the last 3, 6, 9, ... elements if there are at least 3, 6,
9, ... names I suggest to use a simple UDF.

You can find it in sheet Table_Data, E1:H4:
3 6 9
tom 142.00
bob 86.00
ed 40.33

Formulae:
3 6 9
tom =IFERROR(Avg_Last_X($E2,$A:$A,$B:$B,F$1),"") =IFERROR(Avg_Last_X
($E2,$A:$A,$B:$B,G$1),"") =IFERROR(Avg_Last_X($E2,$A:$A,$B:$B,H$1),"")
bob =IFERROR(Avg_Last_X($E3,$A:$A,$B:$B,F$1),"") =IFERROR(Avg_Last_X
($E3,$A:$A,$B:$B,G$1),"") =IFERROR(Avg_Last_X($E3,$A:$A,$B:$B,H$1),"")
ed =IFERROR(Avg_Last_X($E4,$A:$A,$B:$B,F$1),"") =IFERROR(Avg_Last_X
($E4,$A:$A,$B:$B,G$1),"") =IFERROR(Avg_Last_X($E4,$A:$A,$B:$B,H$1),"")

The UDF:
Function Avg_Last_X(s As String, _
rName As Range, rPoint As Range, n As Long) As Double
'Returns average of last n cells in rPoint where s = rName.
'Error value if s does not exist in rName n or more times.
'www.sulprobil.com
Dim r1 As Range, r2 As Range
Dim i As Long, j As Long
Dim d As Double

Set r1 = Intersect(rName, rName.Parent.UsedRange)
Set r2 = Intersect(rPoint, rPoint.Parent.UsedRange)
j = 0
d = 0#
For i = r1.Count To 1 Step -1
If r1(i) = s Then
j = j + 1
d = d + r2(i)
If j >= n Then
Avg_Last_X = d / j
Exit Function
End If
End If
Next i
Avg_Last_X = CVErr(xlErrValue)
End Function

Ignore my worksheet function approach, please (and the other ones as
well :)

Regards,
Bernd
 
J

JimS

Bernd, I've been looking at your spreadsheet and I have another
question. In column C ("True =Last Three"), I don't understand what
this column does. I cleared the contents and the averages still work.
What is the function of this column? Is it necessary for the other
formulas?

Thanks
 
J

JimS

Thank you, Bernd. I noticed the problem with the "column" solution.
Your sheet is very nice. Much appreciated.

And many thanks to all who contributed. I really appreciate the time
and effort. I have more than one workable solution now, and this will
make my project much easier.

Thanks again to all.
 
B

Bernd P

Hello Jim,

That was just a helper column for the pivot table approach. You do not
need it for the macro solution.

As said, I prefer the macro. This is because you encapsulate your
code. Any (more complex) worksheet solution you would have to copy and
to adjust for each different last n. A horror to maintain (the macro
you can maintain at ONE location) ... but: It is your decision :)

Regards,
Bernd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Average Question 13
Sort winners of a golf tournament two ways 15
Changing Averages 4
Horserace Form Revisited 2
indirect average 6
Last 3 rounds Average 6
Averageifs Help!! 2
AVERAGE help, Excel 2003 6

Top