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 Home
ribbon and select Format as Table
and choose any visual format for your data entry table. The reason for doing this is that it assigns a name to the table (probably Table1
by 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 Design
ribbon and looking under Properties
.
![enter image description here](../../I/static/images/39141614dd56d05df36b5f7f4dc30a301c93313ab849484c4f0a1dc18d0827ce.png)
Next, select the entire table, and go to the Insert
ribbon 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 Play
from the field list into the section labeled Row Labels
. Also, drag and drop Times Called
and Yards Gained
into the Values
section. By default these will show up as Sum of Times Called
and Sum of Yards Gained
, which is exactly what you want. This will result in the table you're trying to achieve.
![enter image description here](../../I/static/images/94964ebf33ca172a7d73da65919cefb8f2040e1cc723f3cc1e7215b28821986b.png)
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 Refresh
.
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 Formulas
> Calculate Field...
.
![enter image description here](../../I/static/images/35ae7a9181130fbfa30a2f4a22f7150d12bed174fe0cbc4b5a2d659dbe00130c.png)
Here you'll create a calculated field, called something like YPP. Define the field as
= 'Yards gained'/ 'Times called'
Click 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.
![enter image description here](../../I/static/images/5c9b381d5ba5799fefb59a637b52598c34d3a23f793cf97f5a05790bf59cda2f.png)
Is this a one-time exercise or do you want this to happen automatically? Can we assume that your data is already sorted on Formation + Play. Please update your question with this information. – Jan Doggen – 2014-05-09T13:59:16.243
What do you mean by "average their data"? What is your expected output? Is
Yards gained
a total or an average per play? – Excellll – 2014-05-09T14:00:28.707yards gained is a total and I will continually paste in more data, resort by formation and play but will need to figure out either how to update it automatically or update manually each time I add an additional data set. – Excel is Confusing – 2014-05-09T14:06:10.463
Have you got any experience of working with pivot tables? – CLockeWork – 2014-05-09T15:40:00.677