data selection query - can anyone help.

  • Thread starter Thread starter simon steel
  • Start date Start date
S

simon steel

I have two columns of data something like

1 10
2 11
2 12
2 11
2 13
3 15
4 16
5 17
5 19
5 17
5 17
6 20
6 20
6 20
6 21

From this I want to make another table of data
I want, where the numbers are duplicated in the LH column, to take that
number and put it in the new column once but have the values from the second
column averaged and put in as one value.

e.g.

new column would be

1 10
2 all the numbers corresponding to this value added and averaged.
3 15
4 16
5 all the numbers corresponding to this value added and averaged.


can this be done??


===================================

Alternatively can I form two new columns by taking every fourth pair form
the original columns and putting them into the new columns.
 
You could use a Pivot Table to group the numbers:

1. Add a heading to each column, e.g. Number and Amt
2. Select a cell in the list
3. Choose Data>PivotTable and PivotChart Report
4. Click Next, select the cells with data, click Next
5. Click Layout
6. Drag the Number button to the row area
7. Drag the Amt button to the Data area
8. Double-click the Amt button in the data area,
and summarize by Average
9. Click Next, choose a location for the Pivot Table, click Finish
 
Back
Top