Excel offset average help

[krazyness]

Hey all - been looking for a couple hours and can't come together with an answer. don't know how to ask exact enough, so here we are.

I have a list of players to find an average score. the first score starts in k, l, m, etc... i'll add more to the right infinitely.

i need to average the 3 right most scores. if 0, exclude.
for example:

john smith	"average"	15	0	25	40	0	15
steve smith	"average"	20	20	30	0	0	8

Where john's average would be of 25,40,15
and steve's average would be of 20,30,8.

THEN... when i add another number to the right - it will automatically update the average.

Hopefully that makes sense? i'm sure it's simple for someone much smarter. I think i have to use offset function, but i haven't figured out how to put it into my data.

Thanks!

 

[Ezguy4u]

krazy This is an interesting problem. The criteria of ignoring numbers of equal value is a problem. Deteriorating the top 3 numbers and ignoring numbers of equal value is a challenge.

 

[Peter_SSs]

ignoring numbers of equal value is a problem. .... ignoring numbers of equal value is a challenge.

Did I miss something? You have mentioned that twice, but I don't see it mentioned anywhere.


@krazyness
I would avoid the volatile function OFFSET if at all possible.
What about something like this?

23 08 13.xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	26.66666667	15	0	25	40	0	15
2	19.33333333	20	20	30	0	0	8
3	5	3	2	5	8	9	4	0	0	0	0	0	4			5		0			6
Average

Cell Formulas
Range	Formula
J1:J3	J1	=AVERAGE(TAKE(FILTER(K1:AH1,K1:AH1<>0),,-3))

 

[Ezguy4u]

Peter in the 1st post it says "and steve's average would be of 20,30,8." which to me indicates that he only wants to count the top number of 20 once. Now your formula seems to take this criteria into account and it comes up with the correct answer. But what about row 3. I see 9, 8 and 6 as the top 3 numbers. I believe the average number would be 7.666. Someday I am going to have to get that 365 stuff and up my excel game.

 

[Fluff]

Maybe you missed this part of the op

i need to average the 3 right most scores

Which means (excluding 0) average 4, 5 & 6

 

[krazyness]

Did I miss something? You have mentioned that twice, but I don't see it mentioned anywhere.


@krazyness
I would avoid the volatile function OFFSET if at all possible.
What about something like this?

23 08 13.xlsm
	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	26.66666667	15	0	25	40	0	15
2	19.33333333	20	20	30	0	0	8
3	5	3	2	5	8	9	4	0	0	0	0	0	4			5		0			6
Average

Cell Formulas
Range	Formula
J1:J3	J1	=AVERAGE(TAKE(FILTER(K1:AH1,K1:AH1<>0),,-3))

this seems to work well. i'm adding in more data - but i think that's it. thank you! 1 step down, 6 thousand to go. thanks again.

 

[Peter_SSs]

Peter in the 1st post it says "and steve's average would be of 20,30,8." which to me indicates that he only wants to count the top number of 20 once. Now your formula seems to take this criteria into account and it comes up with the correct answer. But what about row 3. I see 9, 8 and 6 as the top 3 numbers.

Ah, I understand your comment now, though if you look back "top 3" was not mentioned anywhere in the requirements.

this seems to work well. i'm adding in more data - but i think that's it.

You are welcome. Thanks for the follow-up.
Provided, where I have used column AH in the formula (twice), you use a column that is far enough to the right to be sure to include any amount of data you are likely to add, then the formula will continue to average the right-most 3 non-zero numbers.

 

[krazyness]

yes indeed! the three most recent scores is what it will be. i'm building a pretty large golf league workbook. eventually i'd like to automate it more, but for now, i'll make a million formulas and sheets figure it all out!

i had to change a few of them, because i was actually a column off in my explanation. i had a circular reference stuck in one of the sheets that are feeding to the score, and it was wonky for awhile. but all straightened out and i appreciate the help Peter, it appears to be exactly what i needed!

 

[Peter_SSs]

Glad you are making progress.