Excel Question - Averaging odd number of rows

Factual Questions
1

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.

2

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.

3

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:



SELECT values.Time, Avg(Test.Value) AS Average
FROM values
GROUP BY values.Time;
4

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.

5

That’s more like it. I’m actually using OpenOffice, so I’ll give it a shot.

Thanks.

6

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.

7

I figured that much out. :slight_smile:

It worked perfectly, thanks for the help.

8

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.