Average of last 5 values in row that meet condition

**rhigley0126** · 07-05-2014, 08:34 PM

I'm working on an excel file for tracking/analyzing fantasy baseball statistics. As you can see in the attached file, each player is on an individual row and the columns contain 14 different statistics for every day of the season, i.e. columns E through R contain stats for March 31st, columns S through AF contain stats for April 1st and so on. One of the columns for each day contains a "1" if the player started that day and a "0" if they did not. The last column for each day contains the total number of fantasy points they scored for that day.

I'm struggling to come up with a formula that will return the average total fantasy points a player scored in the last five games they started. So the formula would first have to check if the "Start" column for the most recent date contains a "1" and if so, return the average of the value in the corresponding "Total Points" column for that day along with the next four total points from days where the player started.

Not even sure where to start with this one - any help will be greatly appreciated!

**FDibbins** · 07-05-2014, 08:37 PM

Hi and welcome to the forum

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

**rhigley0126** · 07-05-2014, 08:38 PM

Sorry! Same question also posted here:

http://www.mrexcel.com/forum/excel-q...ition-met.html

**rhigley0126** · 07-06-2014, 01:47 PM

Okay, I've given up on trying to average the last 5 games in which a player started. I'm sure it's possible with some sort of array formula or something, but I've been stuck on it for two days now and at this point it's not worth the hassle.

Now I'm just trying to average the Total Points from the last 5 days regardless of whether or not the player started. In cell C3 is today's date, so I'm thinking I can do some sort of AVERAGEIF formula that averages the Total Points if Today's Date - Game Date <=5. However the AVERAGEIF would also need to ensure it's only averaging the values in the "Tot" column for those dates. I've attached an example of what I'm working with - I'm open to any suggestions on format change or anything that will make it easier to come up with these averages. Thanks in advance for any help!

**FDibbins** · 07-06-2014, 10:54 PM

I am out of time right now, will be able to take another look later.

1st, I have transposed your table, excel works better going down than across.

I have started working on your last 5 games inteh attached, using a range of helpers. - Still needs more work on it though. Take a look and see what you think (so far)

**rhigley0126** · 07-07-2014, 12:29 PM

This is great! I think the vertical format will definitely be easier to work with and I'd much rather use helper columns than array formulas as I've heard they can really slow down calculations in large files like this. Thanks so much for the help!

**FDibbins** · 07-07-2014, 01:24 PM

OK I found my error. Change that formula to this...
=AVERAGEIFS(E$5:E$2496,O$5:O$2496,"<="&MAX(O$5:O$2496),O$5:O$2496,">="&MAX(O$5:O$2496)-4,$D$5:$D$2496,$AB5)
copied down and across

You can hide the helpers if you want.

Also, you can adapt that averageif() for the last 5 dates if you ant. Let me know if there is anything else I can help with

**rhigley0126** · 07-07-2014, 02:24 PM

Excellent! Thanks again for the help - I had definitely reached a dead end in my efforts

**FDibbins** · 07-07-2014, 05:00 PM

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

Average of last 5 values in row that meet condition

LinkBack

Thread Tools

Rate This Thread

Display

Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Re: Average of last 5 values in row that meet condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Userform Label visible if two textbox values meet condition

Count unique values within a range that meet a specific condition

Return top values that meet a certain condition

Sum values that are visible and meet a condition [REWARD]

Average cells which meet condition

Bookmarks

Bookmarks

Posting Permissions