• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Averaging from a list of results

P

pjd03

New Member
Hi Peter here. I have a range of results for a sporting competiton. We need to average up to the last 8 results which are displayed in rows. Some competitors will have 4 results some will have 20 - I only want to average each competitors most recent 8. Eg: result - oldest to latest

Person A: N/A,N/A,N/A,95,N/A,96,N/A,N/A,N/A,95,N/A,96,N/A,N/A,N/A,N/A,N/A = 95.5

Person A: N/A,100,95,N/A,96,N/A,N/A,97,95,94,N/A,N/A,96,N/A,94,99,100 = 96.375
 
Hi ,


Can you clarify / confirm one point ?


How is the data organized , in several columns / rows , or is it the way you have posted , several values in a single cell delimited by commas ?


If your data is organized in rows down a particular column , say column A , then the following formula may do what you want :


=AVERAGE(OFFSET($A$2,COUNTA($A:$A)-8-1,,8))


I have assumed your data starts from A2 and goes down , and the N/A is text.


Narayan
 
Thanks Narayan


The data is in a row in separate columns.


Essentially we have rows of competitors and columns are the score of those who competed. The winners scores becomes 100% and all others competing a percetage of their score. eg: Winner achieves 149 out of 150 - that becoimes 100%. Competitor B scores 145 which is then 145/149*100=97.32%. The challenge is that not all epople compete in the same event, regardless I need to calculate the average % of the last 8 scores they have.
 
Hi ,


If your data is in consecutive columns instead of rows , just replace the COUNTA($A:$A) by COUNTA(3:3) assuming your data starts from row 3. For example , suppose you have data in columns C , D , E ,... starting from row #11. Your formula could be :


=AVERAGE((OFFSET($C11,,COUNTA(11:11)-8-1,1,8)))


Copy this downwards. The 11:11 will change to 12:12 , 13:13 ,... , while the $C11 will change to $C12 , $C13 ,...


Narayan
 
Thanks


The only issue is that it seems it averages the 8 inclusive of a Nil result,so it may be average 5 scores (and 3 blanks), rather than selecting the 8 scores and ignoring the blanks.
 
Hi ,


The AVERAGE function ignores blanks ; but are your blanks really cells without any data , or are they displaying no data as a result of formulae ?


This requirement was not mentioned in your original post ; can you clearly mention all aspects of your problem , so that we can get to the solution in the shortest possible time ?


Narayan
 
Narayan (Ninja)


My raw data is 200 competitors who may or may not compete on a given day. Their score is expressed as a % of the winner. I need to take the last actual 8 %'s and average them. My data is arranged from oldest to latest left to right, with each competitor a different row. The first part is easy to do its just a straight % calculation, its averaging up to but not more than 8 results (ignoring gaps), for some competitors they may only have 4 results recorded others may have 30.


Peter
 
Hi Peter ,


Sorry for the oversight ; the part about some competitors having less than 8 results has not been taken care of.


Change the initial formula :


=AVERAGE((OFFSET($C11,,COUNTA(11:11)-8-1,1,8)))


to the following :


=AVERAGE((OFFSET($C11,,MIN(COUNTA(11:11),MAX(0,COUNTA(11:11)-8)),1,MIN(COUNTA(11:11),8))))


Enter this , after making the necessary changes for your data's starting cell , and copy downwards.


Narayan
 
Hi Peter,

Here is how I interpreted your request.

  • You have 1 or more sports scores listed in individual columns next to a player's name.
  • Each player has a distinct row
    The scores may or may not have text (such as N/A) or blanks or zeros.

    The scores are arranged from oldest to most recent, left to right. i.e. the right-most score is the most recent.

    You are looking to average the most recent 8 scores, or if the player has fewer than 8 scores, average of all of those scores, counting only those scores that are greater than zero.

Assuming that your first player's score is in B2:Z2, you can use the following formula to get the average of that player's scores:

=AVERAGE(ROUND(MOD(LARGE(IF(TRANSPOSE(N(TRANSPOSE(B2:Z2))), COLUMN(B2:Z2) + B2:Z2%%), ROW($A$1:INDEX($A:$A, MIN(COUNTIF(B2:Z2, ">0"), 8)))),1)*10^4,3))

Entered with Ctrl + Shift + Enter


For simplicity, I have assumed that the right-most used column (across all players) is Z. (We can get fancy with deducing the last column, etc. if needed.)


Copy the above formula to additional rows, for each player.


Cheers,

Sajan.
 
Sajan


You are exactly correct with your description. I am getting an error in the result/formula cell #VALUE!


Peter
 
Peter,

Since the formula is an array formula, you will need to enter it with Ctrl + Shift + Enter.


-Sajan.
 
You must log in or register to reply here.
Back
Top