Excel Guru's I Need Your Help (1 Viewer)

I'm trying to add up data for a strength of schedule analysis I'm doing.  I want to get an average of stats scored against each team and compare that to actual stats given up.  I've got the data and a schedule grid but I need help automating it.  I'd like to enter the team name and position into cells and have it spit out the average of teams and provide the actual given up.  I've been googling VLOOKUP and INDEX functions but can't seem to get things to work.

Can anyone help?  Any advice would be helpful.  Not sure if I can post attachments in here but I'll look into it and will if I can.

Set up your table with the SOS data as above, teams down the side, a column for each position. The reference for this data if placed in the upper-left is going to be A2:E33 (32 teams and if you add TE). The headings shouldn't be in the cell reference.

Then to grab this data wherever you want, say you input the team in cell X10, the VLOOKUP formula will be:

=VLOOKUP(X10, $A$2:$E$33, 2, 0) ... this will be for QB

For RB, you would change the third variable in the formula from "2" to "3". This represents number of columns to move across in data in the cell reference.

Hope that is explanatory enough as it is late here. Hit me back with questions if you like.

Thanks for your reply Mike.

I'm trying to get average stats for all teams played against.  So 16 sets of stats for each position.  QB stats would be completions, attempts, passing yards, TDs, interceptions, 2PC, rushing attempts, yards, and TDs.  Is VLOOKUP able to grab a row of stats and add and divide them?         

Does anyone know how I can post an excel spreadsheet on here?  Thinking of posting my data and the first person who can make this I would send $20 via paypal.

You can upload a spreadsheet to google drive, or just use a google sheets spreadsheet.

I'm not quite sure what you're trying to do, but it sounds like it might help to use SUMPRODUCT.

There's an Excel help thread in the FFA.

I'm not 100% sure what you're trying to do, but to find means, there's AVERAGE to give you the mean of a column row =AVERAGE(B:B) to find the mean of values in column B. MEDIAN will give median instead of mean. That would give you the total of the column, divided by number of entries.

SUMIFS maybe

Yamato said:

I'm trying to add up data for a strength of schedule analysis I'm doing.  I want to get an average of stats scored against each team and compare that to actual stats given up.  I've got the data and a schedule grid but I need help automating it.  I'd like to enter the team name and position into cells and have it spit out the average of teams and provide the actual given up.  I've been googling VLOOKUP and INDEX functions but can't seem to get things to work.

Can anyone help?  Any advice would be helpful.  Not sure if I can post attachments in here but I'll look into it and will if I can.

Click to expand...

just do it in steps

add a column up and then divide by 16 weeks for your average

then insert a column and compare to the value of the average cell.

I think your problem is more trying to do it all at once

You can and it's more 8th grade math using parenthesis and brackets but split it up into steps and this will get way easier for you

The NFL does this some and DFS sites do, so these types of stats are out there if you feel like hunting

Sorry for not being articulate on this matter.  I've ported the stats to google docs and here it is.  https://docs.google.com/spreadsheets/d/1Lx80LrCW7n6SlZXpYXbzHf6L-9GmSjmk4uC4_khAMY8/edit#gid=543145399

I'll try again to explain what I'm looking to do. Take average team stats from "Team Ave" worksheet for each game played (16 examples).  The teams played against is on the "Sched Grid" worksheet. Arizona plays DET IND DAL SF PHI TB LAR SF SEA HOU JAX LAR TEN WAS NYG SEA.  The average would be of all teams played /16. I then would put these averages next to that teams "vs Stats" worksheet to see the difference in stats (+/-).  Repeat for all 32 teams.

My goal to is compare what team defenses actually gave up compared to what they would have given up if they had performed at their average.  If the team stats are lower than the average of all teams played that defense did better than their numbers say.

I was hunting all day and nowhere do I see stats with as much data as I desire.  If anyone knows of a site that does PLEASE point me in that direction.

I can probably help you out if I can see the SS - but Google's telling me it's private. Can you go in and make it public?

I think I'm set everyone.  Dan Keys just got me pointed in the right direction.  Thanks all.