# Excel Question - Averaging odd number of rows **URL:** \ **Category:** Factual Questions\ **Created:** [September 27, 2005, 4:52am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636 "2005-09-27T04:52:06Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![hightechburrito](https://avatars.discourse-cdn.com/v4/letter/h/f6c823/32.png) [@hightechburrito](https://boards.straightdope.com/u/hightechburrito)\ **Post date:** [September 27, 2005, 4:52am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/1 "2005-09-27T04:52:06Z")
Hey everyone, I’m stumped. What I need to do is average a bunch of different rows of test data, the only problem is that there is no pattern to the number of rows I need to average. A diagram might help a bit here: Time | Value 0 | 0 0 | 1 0 | 2 1 | 2 1 | 3 2 | 4 2 | 5 2 | 6 2 | 5 As you can see, there are 3 rows at time 0, 2 at 1, and four at 2. I need to average all the time 0 values, all the time 1 values, and all the time 2 values. Any ideas? If the list were shorter, I could just enter the equations by hand, but some of the data sets have 15,000 rows, so that’s not an option. Thanks in advance. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [September 27, 2005, 4:55am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/2 "2005-09-27T04:55:25Z")
Use the pivot table function to group all the like times and average the value. The wizard should make it easy to do. Pivot tables are very handy things to learn how to use. The option is under Data on the menus. ---
**Author:** ![ultrafilter](https://avatars.discourse-cdn.com/v4/letter/u/3d9bf3/32.png) [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)\ **Post date:** [September 27, 2005, 4:59am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/3 "2005-09-27T04:59:12Z")
If you have MS Access, this sort of thing is easy to do. Import your data into a table called values and put this as the SQL for a new query: ```auto SELECT values.Time, Avg(Test.Value) AS Average FROM values GROUP BY values.Time; ``` ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [September 27, 2005, 5:18am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/4 "2005-09-27T05:18:05Z")
And if you don’t want to do that you can do this sort by Time in C2 **=IF(A1=A2,C1+1,1)** in D2 **=IF(A1=A2,D1+B2,B2)** in E2 **=D2/C2** in F2 **=IF(A2=A3,0,1)** select data filter autofilter and display only the **1** values in F C,D and E tell you number times with values, total of values for that time, average value for that time. ---
**Author:** ![hightechburrito](https://avatars.discourse-cdn.com/v4/letter/h/f6c823/32.png) [@hightechburrito](https://boards.straightdope.com/u/hightechburrito)\ **Post date:** [September 27, 2005, 5:26am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/5 "2005-09-27T05:26:46Z")
> [@don't ask](#): > > And if you don’t want to do that you can do this > > sort by Time > > in C2 **=IF(A1=A2,C1+1,1)** > > in D2 **=IF(A1=A2,D1+B2,B2)** > > in E2 **=D2/C2** > > in F2 **=IF(A2=A3,0,1)** > > select data filter autofilter and display only the **1** values in F > > C,D and E tell you number times with values, total of values for that time, average value for that time. That’s more like it. I’m actually using OpenOffice, so I’ll give it a shot. Thanks. ---
**Author:** ![don\_t\_ask](https://avatars.discourse-cdn.com/v4/letter/d/e68b1a/32.png) [@don\_t\_ask](https://boards.straightdope.com/u/don_t_ask)\ **Post date:** [September 27, 2005, 5:26am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/6 "2005-09-27T05:26:47Z")
I forgot to say copy the formulas to the bottom. It is a longwinded way of doing it but would only take a few minutes and once you start using formulas you get better at finding ways to manipulate Excel data. You can recycle solutions for one problem many times. ---
**Author:** ![hightechburrito](https://avatars.discourse-cdn.com/v4/letter/h/f6c823/32.png) [@hightechburrito](https://boards.straightdope.com/u/hightechburrito)\ **Post date:** [September 27, 2005, 5:37am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/7 "2005-09-27T05:37:39Z")
> [@don't ask](#): > > I forgot to say copy the formulas to the bottom. I figured that much out. 🙂 > [@](#): > > It is a longwinded way of doing it but would only take a few minutes and once you start using formulas you get better at finding ways to manipulate Excel data. You can recycle solutions for one problem many times. It worked perfectly, thanks for the help. ---
**Author:** ![Coil](https://avatars.discourse-cdn.com/v4/letter/c/bb73d2/32.png) [@Coil](https://boards.straightdope.com/u/Coil)\ **Post date:** [September 27, 2005, 10:40am UTC](https://boards.straightdope.com/t/excel-question-averaging-odd-number-of-rows/323636/8 "2005-09-27T10:40:06Z")
While I am sure the other ways work, there may be an easier solution. (I am using the Swedish language version of Excel so my translations of menu-names could be a bit off). Go in under Data in the menu (the heading where you have functions like Sorting and Filters). Then choose the “Sums” command (It is called Delsummor in my version, don’t know the English word though). It will allow you to mark an area and you can get sums, averages, max, mins etc of any number of columns. You also choose when to sum so one can do such things as calculate the sums of sales at every change in the month column.