Average of last 5 values in row if condition met??

[rhigley0126]

I'm working on an excel file for tracking/analyzing fantasy baseball statistics. 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!

 

[hateme28]

could you post an example table or part of what you have to explain a little better?

 

[rhigley0126]

Below is the last two days worth of data for 20 different players. I have also shared an example of the excel data here:

http://www.filedropper.com/fantasybaseballexample070514

As you can see, yesterday the last player on the list did not start, yet still scored points (walked and scored a run after pinch-hitting later in game) but I don't want to count that in the last 5 games average. In the file from the link above, you can see that his total points average would actually be 5.00 (counting 6/28, 6/29, 6/30, 7/2 and 7/3 - excluding 7/4 because the "Strt"=0 and excluding 7/1 because his team was off that day, hence the blanks).

 

[FDibbins]

cross posted here...
Average of last 5 values in row that meet condition

 

[hateme28]

Something like this should be a good start.

=AVERAGE(IF(EC5=1,ED5,),IF(DO5=1,DP5,),IF(DA5=1,DB5,),IF(CM5=1,CN5,),IF(BY5=1,BZ5,))

You will need to manually input it into each cell that requires an average and adjust each cell affected. It'll take some time to make it accurate.

 

[rhigley0126]

I was working off of something similar to that at one point but I couldn't figure out how to get it to stop adding more numbers to average once it had reached five.

 

[rhigley0126]

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. Below is a link to 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!​

http://www.filedropper.com/dailyfantasybaseballexample070514

 

[Snakehips]

rhigley0126,


Maybe try this....

Excel 2007
	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY
3	04/07/2014														Last 5 Games Average
4	H/A	Opp	Strt	1B	2B	3B	HR	RBI	R	BB	HbP	SB	CS	Tot	1B	2B	3B	HR	RBI	R	BB	HbP	SB	CS	Tot
5	H	sea	1.00	0.00	0.00	0.00	10.00	4.00	2.00	0.00	0.00	0.00	0.00	16.00											9.20
6	A	chw	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00											5.60
7	H	mia	1.00	3.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.00											4.00
8																									0.00
9																									0.00
10																									0.00
11	H	phi	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00											6.20
12	A	nym	1.00	9.00	0.00	0.00	0.00	0.00	2.00	0.00	0.00	5.00	0.00	16.00											6.40
13																									0.00
14	H	nyy	1.00	0.00	0.00	8.00	0.00	2.00	2.00	2.00	2.00	5.00	0.00	21.00											7.40
15	H	lad	1.00	3.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	3.00											0.00
16																									0.00
17	A	sdg	1.00	0.00	5.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	5.00											0.00
18	A	pit	1.00	0.00	0.00	0.00	0.00	0.00	0.00	2.00	0.00	0.00	0.00	2.00											4.80
19	H	tam	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00											5.60
20	H	hou	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00											2.00
21																									0.00
22	H	lad	1.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00											0.00
23	A	was	1.00	6.00	0.00	0.00	0.00	0.00	2.00	0.00	0.00	0.00	0.00	8.00											7.20
24	A	nym	0.00	0.00	0.00	0.00	0.00	0.00	2.00	2.00	0.00	0.00	0.00	4.00											5.00
Player Data

Cell Formulas
Range	Formula
EY5	{=IF(SUM(($G5:$EC5=1)*(MOD(COLUMN($G5:$EC5)-7,14)=0))>4,SUM(($G5:$EC5=1)*(MOD(COLUMN($G5:$EC5)-7,14)=0)*(IF(ISNUMBER($R5:$EN5),$R5:$EN5,0))*(COLUMN($R5:$EN5)>LARGE((MOD(COLUMN($R5:$EN5)-18,14)=0)*($G5:$EC5=1)*COLUMN($G5:$EC5),6)+11))/5,0)}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.