Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with averages please
Hi Guys,
This is probably really easy but I have been struggling to average the values in 2 linked collumns. eg A B 2 3 2 4 2 3 3 2 3 2 4 1 5 2 5 3 What I want to happen is it to calculate the average of the values in the second column that match the same variable in the first column, and paste them into a seperate row, so the formula would calculate A B 2 (Average(3+4+3)) 3 (Average(2+2)) 4 1 5 (Average(2+3)) to produce these columns A B 2 3.33 3 2 4 1 5 2.5 I have been trying to write a macro but with no luck. Please help. Many thanks in advance for looking. Amer Karim |
#2
|
|||
|
|||
Hi!
List the unique numbers from column A in another column. If there are a lot of them you can use an advanced filter to do this for you. Assume that unique list is in the range E1:E4. In F1 enter this formula and copy down: =SUMIF(A$1:A$8,E1,B$1:B$8)/COUNTIF(A$1:A$8,E1) An alternative: Array entered in F1 and copied down: =AVERAGE(IF(A$1:A$8=E1,B$1:B$8)) Biff "amerkarim" wrote in message ... Hi Guys, This is probably really easy but I have been struggling to average the values in 2 linked collumns. eg A B 2 3 2 4 2 3 3 2 3 2 4 1 5 2 5 3 What I want to happen is it to calculate the average of the values in the second column that match the same variable in the first column, and paste them into a seperate row, so the formula would calculate A B 2 (Average(3+4+3)) 3 (Average(2+2)) 4 1 5 (Average(2+3)) to produce these columns A B 2 3.33 3 2 4 1 5 2.5 I have been trying to write a macro but with no luck. Please help. Many thanks in advance for looking. Amer Karim -- amerkarim |
#3
|
|||
|
|||
One way is to use a pivot table (only a couple of clicks will get us there
!) Assuming the posted data is in A1:B9, with headers "A", "B" in A1:B1 Select any cell within A1:B9 Click Data Pivot Table Report Click Next Next In Step 3 of the wizard: Drag and drop "A" within the ROW area Drag and drop "B" within the DATA area (It'll appear as "Sum of B") Double-click on "Sum of B" and in the dialog: Select "Average" under "Summarize by" Click "Number.." Number 2 d.p. (say) OK Click OK at the main dialog ("Sum of B" will have changed to "Average of B") Click Finish The pivot table will be created in a new sheet to the left, and yield the desired results: Average of B A Total 2 3.33 3 2.00 4 1.00 5 2.50 Grand Total 2.50 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "amerkarim" wrote in message ... Hi Guys, This is probably really easy but I have been struggling to average the values in 2 linked collumns. eg A B 2 3 2 4 2 3 3 2 3 2 4 1 5 2 5 3 What I want to happen is it to calculate the average of the values in the second column that match the same variable in the first column, and paste them into a seperate row, so the formula would calculate A B 2 (Average(3+4+3)) 3 (Average(2+2)) 4 1 5 (Average(2+3)) to produce these columns A B 2 3.33 3 2 4 1 5 2.5 I have been trying to write a macro but with no luck. Please help. Many thanks in advance for looking. Amer Karim -- amerkarim |
#4
|
|||
|
|||
On Wed, 28 Sep 2005 01:17:00 +0100, amerkarim
wrote: Hi Guys, This is probably really easy but I have been struggling to average the values in 2 linked collumns. eg A B 2 3 2 4 2 3 3 2 3 2 4 1 5 2 5 3 What I want to happen is it to calculate the average of the values in the second column that match the same variable in the first column, and paste them into a seperate row, so the formula would calculate A B 2 (Average(3+4+3)) 3 (Average(2+2)) 4 1 5 (Average(2+3)) to produce these columns A B 2 3.33 3 2 4 1 5 2.5 I have been trying to write a macro but with no luck. Please help. Many thanks in advance for looking. Amer Karim Ensure the two columns have labels at the top as in your example (A & B) Select some cell in your table. Data/Subtotals At each change in "A" Use function "Average" Add subtotal to "B" If need be, you can easily shift the cells over --ron |
#5
|
|||
|
|||
Thanks Max,
It worked a treat. How you guys get so good at this stuff just kills me. Many, many thanks again with my kind regards, Amer Karim. Quote:
|
#6
|
|||
|
|||
You're welcome !
-- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trouble Sorting Averages of Randomly Generated Numbers | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
how to use the Prob function in a list of averages to find the le | Excel Worksheet Functions | |||
Difference between Averages in a Pivot Table | Excel Worksheet Functions | |||
Converting Weekly Data into Monthly Averages | Excel Discussion (Misc queries) |