| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use TrimMean as an agregation function
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to use TrimMean as an agregation function
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. -- Regards, Bill "Nicolae Panait" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel Data Validation/Lookup function does function correcty | Excel Worksheet Functions | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| TRIMMEAN with different percentiles? | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Trimmean for limited array | Excel Worksheet Functions | |||
Linear Mode
