Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
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. -- HTH, Dianne In , SS typed: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
thanks - I'll look into this!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
A SumIF()/CountIF() is the best way to do it through a pivot table could do the same thin
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
can you explain further?
"Nichevo" wrote in message ... A SumIF()/CountIF() is the best way to do it through a pivot table could do the same thing |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
data selection query no1.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use MS Query to query data within the current work book | Excel Discussion (Misc queries) | |||
Using Query for a selection of items | Excel Discussion (Misc queries) | |||
Using a ListBox selection to query a database | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) |