How to find average in groups of 3 entries

Every time I think I have this working, someone in our group changes the criteria. The attached has the latest formula to solve my averaging problem. I have a new problem that may not be solvable.
The goal is for the value in column M to be the average of the numbers in P through AAD. The first complication was that we only wanted the latest X number of stats to apply to the average. X is the value in B1 and the formula there now works fine.
However, now I have been asked to make it so the value in M only changes on mulitiples of the value in B (currently 3).
In the sample the value in M for Richard Aaron should be 442 and remain that way until there are 6 stat values.

I am not sure I am explaining this well. Please take a look and if there are questions or you need more info to help me figure this out let me know.

sample.xlsx

Thanks!

Okay.. if you got 6 weeks of data but B1 says 5 then what is your expected results ?


Regards,
DILIPandey
<click on below * if this helps>

If B1 was 5 then the average should be for the first 5 of the 6 that I have. It should stay the same until I have 10 weeks of data for that player. Currently B1 is 3 and is probably staying there for the forseeable future, so if it is easier to do by removing the use of B1 as a variable that would not be a problem.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

HI Jimbo,

Would love to help if you first follow the above suggestion by Fdibbins.


Regards,
DILIPandey
<click on below * if this helps>

Well,
I've changed it. I hope it is better.

Currently B1 is 3 and is probably staying there for the forseeable future, so if it is easier to do by removing the use of B1 as a variable that would not be a problem.

sorry..I am still not clear.

Let me know if I am correct as per below table :-

data available for no. of wks = consider no. of wks for average
------------------------------------------------------------
03 = 3
04 = 3
05 = 3
06 = 6
07 = 6
08 = 6
09 = 9
10 = 9
11 = 9
and so on ?

Regards,
DILIPandey
<click on below * if this helps>

You are correct. So whatever I use can't just pull the three latest weeks. If there are 4 weeks available, the average needs to be based on weeks 1, 2, and 3. If there are 6 weeks available then it needs to be based on 4, 5, and 6.

Hi Jimbo,

See the attached file where I have used below formula in define name "data" :-

Formula:

Please Login or Register  to view this content.

sample.xlsx

Regards,
DILIPandey
<click on below * if this helps>

Wow... this seems to work exactly as I need it to! I have two questions. First, how do I add this to my spreadsheet? I don't see where the actual formula is. The other is are we using the value of B1 to set how many weeks, or is it hard coded at 3?

HI Jimbo,

You can replace 3 with B1 in the formula if you want to use B1.
And,
In order to use this in your spreadhseet... copy the formula and the defined name, which you can get by pressing ctrl + F3.


Regards,
DILIPandey
<click on below * if this helps>

Once again thanks!! I think i've got it working on the real spreadsheet now. I am going to mess with it tomorrow to make sure all the math works right, and then put it into use. I really appreciate the time it must have taken you to work this all out for me.

Last question. Which 3 do I change to B1?

start with changing all 3



Regards,
DILIPandey
<click on below * if this helps>

That was my thinking, but it's nice to confirm. I did however forget to enter it as $b$1, so it didn't work at first.

Thanks again for all your help!

You are welcome Jimbo..



Regards,
DILIPandey
<click on below * if this helps>