Averaging x amount of values not including blanks from a maximum of y columns

[dsdavis6]

Hi,
I have data that looks like this

M N O P Q R S T U V X Y Z AA AB
7 7 7 7 6 7 6 5 5 7 6 7 7 8
5 7 7
8 6 6
8 6 7 7 7 6 7 5 7 6 6 6 8 6 8
5 5 6 7 6 6 6
8 4 6 7 7 8 6 7 6 7 7 6 6 7 6 7
7 7 7 7 5 6 5 6 7 7 6 8
6 5 6
and so on.....
Column's F,G,H and contain a names, Count and Average formulas.

In column C and D I would like to be able to put two values in, C the amount of values it will look at not including blanks, Column D the maximum number of columns I would like it to look at with Column E then giving me the average of the numbers in the columns matching that criteria. eg I want to average the first 5 Weeks available but within a maximum of 10 weeks, the 5th row down would only be able to use 4 numbers before it would go into the 11th column.

I hope I am being clear and sorry if this information is available somewhere I have searched but I am really struggling to find anything, it is probably more I don't really know the names of the functions I am looking for more than anything else though. Any help would be greatly appreciated.
Daniel

 

[Matty]

Welcome to the Board!

I'm struggling to understand exactly what you're wanting to do.

Could you post a small example as well as the results(s) your want the formula(s) to deliver?

Best way to post data is to ensure there are borders around your Cells, select the data, hit copy and then paste into the body of your message.

Matty

 

[dsdavis6]

Sorry I did put an example but I didn't realise that it would take all of the formatting out of the data i posted. There was column spaces.

So basically I would like a formula for column E. I want it to look from Column J to end of sheet. So in the Graham Alexander example, I want it to average the next 7(C) columns with data in starting from J but only up to a maximum of 10(D). So in this case there are two 6's only which of course average to 6.

If this is too difficult I would make do without Column D and have it just look for the next 7 values from J.

Am I making more sense now? Thanks for taking the time!
Daniel

 

[Matty]

Hi,

I think I understand. But what do Columns C and D denote? Should Column D be used as the last Column to reference? For example, a Max Weeks of 10 would mean Column V (26/02/2011) is the last week and the Avg Rating for Lee Grant would be 6.2. Is this right?

Moreover, if you post data, try to post actual data rather than an image. Would-be helpers can then simply copy the data to do testing on.

Matty

 

[dsdavis6]

Well when I pasted it, it seemed to remove the column spacing in between some of the numbers once I actually posted it which of course made the example quite difficult to understand. Column C and D are just for me to get an idea of short term form for a player. For example Tyrone Mears has an overall average of 6.68 over 22 weeks worth of data, but I may prefer to look at about up to 10 games in up to 15 weeks worth of data.

Your example is correct but I would like it to start from J. I know there is no data there but there will be as this is something I will update each week and I have left those spare columns so I can insert new columns without it screwing with any formulas. I will factor in the empty columns when I put in my criteria in Column C and D. The line break is there to indicate the end of a football season.

 

[Matty]

A formula like this:

=AVERAGE(INDEX(J$3:AH$3,MATCH(TRUE,J$1:AH$1>0,0)):INDEX(J$3:AH$3,MATCH(TRUE,J$1:AH$1>0,0)+D3-1))

Array entered, i.e. with CTRL+SHIFT+ENTER, would allow you to get an average of the non-empty Cells in a dynamic range, where the value in Column D determines how many weeks back to look from the where the first date exists.

I can't see what Rows your data appears in, so I've assume Row 1 contains the dates and gone from there.

Matty

 

[dsdavis6]

Thank you so much for your patience and help