Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting data
I have a 3 column table with about 56,000 rows (temperature, weather station
and year data). I'm trying to sum the data in the first row for a range defined by the same number in the second row. The ranges vary. I want to then average the summed data by the range. Here's a small sample of the data. Temp. WS # Year 23.23333333 8594 1971 22.90833333 8594 1972 23.175 8594 1973 22.34166667 8594 1974 18.825 40608 1926 18.76666667 40608 1927 18.63333333 40608 1928 19.14166667 40608 1929 19.05 40608 1930 18.66666667 40608 1931 26.48333333 42731 1907 26.05 42731 1908 25.95833333 42731 1909 26.10833333 42731 1910 25.64166667 42731 1911 26.35833333 42731 1912 25.93333333 42731 1913 26.16666667 42731 1914 From this sample of the data I am looking for the following: Average WS # number of counts 22.91458 8594 4 18.84722 40608 6 26.0875 42731 8 If possible, at the same time I'd also be intersted in calculating the standard deviation of the first colum. Thanks! Ian |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting data
One quick way is to try a pivot table ..
Select a cell within the source table Click Data PivotTable ... Click Next Next In Step 3, click Layout Drag n drop WS # into ROW Drag n drop WS # into DATA It'll appear as "SUM of WS #" Double-click on it, change Summarize by to: Count, click OK Drag n drop Temp. into DATA, just below the earlier "Count of WS #" Double-click on it, change Summarize by to: Average, click OK Drag n drop Temp. again into DATA, just below the former one Double-click on it, change Summarize by to: StdDev, click OK Click OK Finish Then go to the PT sheet, drag the field DATA and drop over "Total", and you'd get the table that you're after, with 4 cols: WS #, Count of WS #, Average of Temp., StdDev of Temp. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "I. Miller" wrote: I have a 3 column table with about 56,000 rows (temperature, weather station and year data). I'm trying to sum the data in the first row for a range defined by the same number in the second row. The ranges vary. I want to then average the summed data by the range. Here's a small sample of the data. Temp. WS # Year 23.23333333 8594 1971 22.90833333 8594 1972 23.175 8594 1973 22.34166667 8594 1974 18.825 40608 1926 18.76666667 40608 1927 18.63333333 40608 1928 19.14166667 40608 1929 19.05 40608 1930 18.66666667 40608 1931 26.48333333 42731 1907 26.05 42731 1908 25.95833333 42731 1909 26.10833333 42731 1910 25.64166667 42731 1911 26.35833333 42731 1912 25.93333333 42731 1913 26.16666667 42731 1914 From this sample of the data I am looking for the following: Average WS # number of counts 22.91458 8594 4 18.84722 40608 6 26.0875 42731 8 If possible, at the same time I'd also be intersted in calculating the standard deviation of the first colum. Thanks! Ian |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting data
Beautiful!! Well done, and nice instructions! Much appreciated.
"Max" wrote: One quick way is to try a pivot table .. Select a cell within the source table Click Data PivotTable ... Click Next Next In Step 3, click Layout Drag n drop WS # into ROW Drag n drop WS # into DATA It'll appear as "SUM of WS #" Double-click on it, change Summarize by to: Count, click OK Drag n drop Temp. into DATA, just below the earlier "Count of WS #" Double-click on it, change Summarize by to: Average, click OK Drag n drop Temp. again into DATA, just below the former one Double-click on it, change Summarize by to: StdDev, click OK Click OK Finish Then go to the PT sheet, drag the field DATA and drop over "Total", and you'd get the table that you're after, with 4 cols: WS #, Count of WS #, Average of Temp., StdDev of Temp. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "I. Miller" wrote: I have a 3 column table with about 56,000 rows (temperature, weather station and year data). I'm trying to sum the data in the first row for a range defined by the same number in the second row. The ranges vary. I want to then average the summed data by the range. Here's a small sample of the data. Temp. WS # Year 23.23333333 8594 1971 22.90833333 8594 1972 23.175 8594 1973 22.34166667 8594 1974 18.825 40608 1926 18.76666667 40608 1927 18.63333333 40608 1928 19.14166667 40608 1929 19.05 40608 1930 18.66666667 40608 1931 26.48333333 42731 1907 26.05 42731 1908 25.95833333 42731 1909 26.10833333 42731 1910 25.64166667 42731 1911 26.35833333 42731 1912 25.93333333 42731 1913 26.16666667 42731 1914 From this sample of the data I am looking for the following: Average WS # number of counts 22.91458 8594 4 18.84722 40608 6 26.0875 42731 8 If possible, at the same time I'd also be intersted in calculating the standard deviation of the first colum. Thanks! Ian |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sorting data
Welcome. Glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- On Apr 2, 12:30 pm, I. Miller wrote: Beautiful!! Well done, and nice instructions! Much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Data | Excel Discussion (Misc queries) | |||
Help please - Data sorting | Excel Worksheet Functions | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
colors of bar charted data don't follow data after sorting | Charts and Charting in Excel | |||
Sorting data | New Users to Excel |