Okay here is what I have. How would I get the three identical wishbone plays to merge and sum their data?
Yes the data is already sorted by formation. I don’t mind inputing it each time as I will probably add to the list several additional plays and formations. I just want to merge the times called together and the yards gained together. So the FSU RB1 OT should only be one cell and say 5 for times called and 18 for yards gained. I can do math in my head mostly to figure out the average. Sorry for not being specific enough.
A nice solution for this is to use a pivot table. A pivot table allows you to summarize data from your table. It can be used to keep a running total for each play type and calculate the average yards per play. With a pivot table, your data entry can be simple — just add the new data to the end of the table and let the pivot table update the totals for you.
To set up your pivot table do the following:
Select your entire table, including headers. Go to the
Homeribbon and select
Format as Tableand choose any visual format for your data entry table. The reason for doing this is that it assigns a name to the table (probably
Table1by default), and the name will refer to the entire table even if you add new data. You can find the name of the table by going to the
Table Tools Designribbon and looking under
Next, select the entire table, and go to the
Insertribbon and click
Pivot Table. A dialog will appear asking you which source data to use and where to place the pivot table. Make sure the source is set to the name of your table, e.g.,
Table1. I would advise placing the pivot table on a new sheet, but you may put it on the same sheet if you like — just make sure it’s off to the side out of the way from your raw data. After choosing your output location, click OK.
You will be presented with something like this:
You’ll use the Field List on the right to set up your pivot table. For your particular needs, you’ll want to drag and drop
Playfrom the field list into the section labeled
Row Labels. Also, drag and drop
Yards Gainedinto the
Valuessection. By default these will show up as
Sum of Times Calledand
Sum of Yards Gained, which is exactly what you want. This will result in the table you’re trying to achieve.
Any time you’ve added new data and want to update the summary, just go to the pivot table, right-click anywhere inside the table and choose
If you’d like to include the average yards per play in the pivot table, you will need to create a calculated field. To do this click somewhere inside the pivot table and go to the
PivotTable Tools Options ribbon and click
Here you’ll create a calculated field, called something like YPP. Define the field as
= 'Yards gained'/ 'Times called'
Add and then click
OK. Your pivot table will now include a
Sum of YPP field. This should display the average yards per carry for each play.