data selection query no1.

  • Thread starter Thread starter SS
  • Start date Start date
S

SS

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.
 
Would you consider using a pivot table? If necessary, add a row at the
top of your data that contains the field names.

XL97: Click into your data and select Data > Pivot Table Report. When
the Pivot Table Wizard appears, click Next to accept the default
"Microsoft Excel List or Database". Click Next if the Wizard has
correctly detected your database range. Then drag Field1 from the list
on the right into the area that is marked ROW and drag Field2 into the
area marked DATA. This will probably be converted into "Sum of Field2".
If so, double-click on "Sum of Field2" and change "summarize by" from
Sum to Average. Click OK to accept this change and then click Finish to
close the Wizard and place your pivot table into a new sheet.

If this isn't suitable or you need more help with pivot tables, post
back.
 
A SumIF()/CountIF() is the best way to do it through a pivot table could do the same thin
 
Ok Data is in Column A and B from Rows 2 to 16 (Headers in Row 1)

ColumnA Column
1 1
2 1
2 1
2 1
2 1
3 1
4 1
5 1
5 1
5 1
5 1
6 2
6 2
6 2
6 2

Column of Numbers from 1 to 6 is in Column D and in Column E we have the formul

ColumnD Column
1 1
2 11.7
3 1
4 1
5 17.
6 20.2

formula is as follow

=IF(COUNTIF($A$2:$A$16,D2)>0,SUMIF($A$2:$A$16,D2,$B$2:$B$16)/COUNTIF($A$2:$A$16,D2),

Which is just copies down that colum

the formula first checks if there is any of that number in the first list (Avoids a Div/O error if one of the numbers was not in the first list) and you can probally leave out this IF if you want to make it as follow

=SUMIF($A$2:$A$16,D2,$B$2:$B$16)/COUNTIF($A$2:$A$16,D2

The sumif adds up the column B if column A is equal to the value in cell D in the above case D2 and the countif counts the number of times in column A the value in cell D occurs

if you write the formulas into excel it should give you more information about what is required for the sumif and countif - both require a range and criteria to match with that range and the sumif also needs to know which column to sum up if it gets a true
 
Back
Top