How to use TrimMean as an agregation function

[Nicolae Panait]

Hi all,
Can anyone help me calculating TrimMean values(of HourlyValue field) in a
data set containing {Date, Date_Hour, HourlyValue} for each change in the
Date field!
Examples:
DateHour
HourlyValue
Date
TrimMean


2004-04-18 23
817
18-Apr-04
853
Trim mean (with 95%) on all values having date='18-Apr-04' (the value
from relative C column)

2004-04-18 22
859
18-Apr-04
853


2004-04-18 21
843
18-Apr-04
853


2004-04-18 20
817
18-Apr-04
853


2004-04-18 19
804
18-Apr-04
853


2004-04-18 18
852
18-Apr-04
853


2004-04-18 17
843
18-Apr-04
853


2004-04-18 16
864
18-Apr-04
853


2004-04-18 15
849
18-Apr-04
853


2004-04-18 14
877
18-Apr-04
853


2004-04-18 13
902
18-Apr-04
853


2004-04-18 12
873
18-Apr-04
853


2004-04-18 11
806
18-Apr-04
853


2004-04-18 10
822
18-Apr-04
853


2004-04-18 09
857
18-Apr-04
853


2004-04-18 08
854
18-Apr-04
853


2004-04-18 07
852
18-Apr-04
853


2004-04-18 06
813
18-Apr-04
853


2004-04-18 05
812
18-Apr-04
853


2004-04-18 04
911
18-Apr-04
853


2004-04-18 03
1007
18-Apr-04
853


2004-04-18 02
982
18-Apr-04
853


2004-04-18 01
1032
18-Apr-04
853


2004-04-18 00
898
18-Apr-04
853
#

2004-04-17 23
952
17-Apr-04
842
<--The date value changes from above and

2004-04-17 22
1011
17-Apr-04
842


2004-04-17 21
809
17-Apr-04
842


2004-04-17 20
831
17-Apr-04
842


2004-04-17 19
817
17-Apr-04
842


2004-04-17 18
796
17-Apr-04
842


2004-04-17 17
836
17-Apr-04
842


2004-04-17 16
862
17-Apr-04
842


2004-04-17 15
850
17-Apr-04
842


2004-04-17 14
877
17-Apr-04
842


2004-04-17 13
837
17-Apr-04
842


2004-04-17 12
841
17-Apr-04
842


2004-04-17 11
843
17-Apr-04
842


2004-04-17 10
840
17-Apr-04
842


2004-04-17 09
835
17-Apr-04
842


2004-04-17 08
823
17-Apr-04
842


2004-04-17 07
831
17-Apr-04
842


2004-04-17 06
835
17-Apr-04
842


2004-04-17 05
854
17-Apr-04
842


2004-04-17 04
935
17-Apr-04
842


2004-04-17 03
965
17-Apr-04
842


2004-04-17 02
977
17-Apr-04
842


2004-04-17 01
968
17-Apr-04
842


2004-04-17 00
977
17-Apr-04
842




Thanks in advance,
Nicolae

 

[Bill Renaud]

Is the data really in a single vertical column ($A) as shown in your post,
or is it in a list across columns $A:$E? If it is in a single column, then
you will need to transpose it into a list format first. Then...

1. Build a pivot table from this data on a new worksheet.
2. Arrange the pivot table so that the Date is in a row area of the pivot
table (column $A), Hour is in the column area, and the HourlyValue is in the
data area.
3. Build the TrimMean formula in cells adjacent to the pivot table on the
right.