Summarizing Data

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi, I have a Excel document that currently looks something
like this:

0 0 3.6
0 0 1.1
0 1 1.2
0 1 2.4
1 0 2.6
1 0 6.9

I want to summarize it (by column? by row? I don't know)
so that I get the average of the last column for each
unique first and second column pair:

0 0 [Avr: 3.6, 1.1]
0 1 [Avr: 1.2, 2.4]
1 0 [Avr: 2.6, 6.9]

Obviously the data det is very large, and so I can't do it
by hand. Any way to do this?
Thanks,
Mike
 
Will do, thanks.

-----Original Message-----
Hi Mike
have a look at pivot tables. For getting startet check
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards
Frank Kabel
Frankfurt, Germany
Hi, I have a Excel document that currently looks something
like this:

0 0 3.6
0 0 1.1
0 1 1.2
0 1 2.4
1 0 2.6
1 0 6.9

I want to summarize it (by column? by row? I don't know)
so that I get the average of the last column for each
unique first and second column pair:

0 0 [Avr: 3.6, 1.1]
0 1 [Avr: 1.2, 2.4]
1 0 [Avr: 2.6, 6.9]

Obviously the data det is very large, and so I can't do it
by hand. Any way to do this?
Thanks,
Mike
Click to expand...
.
Click to expand...
 
A formula-based approach...

Let A1:C7 house the sample with labels added:

{"X","Y","Z";0,0,3.6;0,0,1.1;0,1,1.2;0,1,2.4;1,0,2.6;1,0,6.9}

In D1 enter the label: Concat

In D2 enter & copy down:

=A2&CHAR(127)&B2

Apply to D1:D7 a formula system or Advanced Filter with Unique records only
checked in order to create a list of unique combinations in, say, column F
from F1 on, which would give:

{"Concat";"00";"01";"10"}

In G1 enter the label: Avg

In G2 enter & copy down:

=SUMIF($D$2:$D$7,F2,$C$2:$C$7)/COUNTIF($D$2:$D$7,F2)

The results area in F:G will now house:

{"Concat","Avg";"00",2.35;"01",1.8;"10",4.75}
 
One thing you might want to try when creating your Pivot Table, is to firstly
put an extra column of data in your table. Assuming your two first columns are
A and B starting A2, then in say Col D in cell D2 put =A2&" "&B2 and copy down.
make sure you give that column a header (and that the others also have one)

Now create your pivot table by selecting all your data, then doing Data / Pivot
table and Pivot Chart report, hit Next / Next / Finish.

Drag whatever your new header is to the left of the table and then drag the
values column header to the centre. Right click on the values, choose field
settings and then click on AVERAGE instead of SUM.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Mike said:
Will do, thanks.

-----Original Message-----
Hi Mike
have a look at pivot tables. For getting startet check
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm

--
Regards
Frank Kabel
Frankfurt, Germany
Hi, I have a Excel document that currently looks something
like this:

0 0 3.6
0 0 1.1
0 1 1.2
0 1 2.4
1 0 2.6
1 0 6.9

I want to summarize it (by column? by row? I don't know)
so that I get the average of the last column for each
unique first and second column pair:

0 0 [Avr: 3.6, 1.1]
0 1 [Avr: 1.2, 2.4]
1 0 [Avr: 2.6, 6.9]

Obviously the data det is very large, and so I can't do it
by hand. Any way to do this?
Thanks,
Mike
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top