sorting data

[Guest]

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

 

[Guest]

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.

 

[Guest]

Beautiful!! Well done, and nice instructions! Much appreciated.

 

[Max]

Welcome. Glad it helped.