Excel offset average help

krazyness

New Member
Joined
Jan 31, 2017
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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"1502540015
steve smith"average"202030008

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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.
 
Upvote 0
ignoring numbers of equal value is a problem. .... ignoring numbers of equal value is a challenge.
:confused: 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
JKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
126.666666671502540015
219.33333333202030008
35325894000004506
Average
Cell Formulas
RangeFormula
J1:J3J1=AVERAGE(TAKE(FILTER(K1:AH1,K1:AH1<>0),,-3))
 
Last edited:
Upvote 0
Solution
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.
 
Upvote 0
:confused: 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
JKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
126.666666671502540015
219.33333333202030008
35325894000004506
Average
Cell Formulas
RangeFormula
J1:J3J1=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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,223,991
Messages
6,175,820
Members
452,672
Latest member
missbanana

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