Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging unique values
Hi all,
I have rows of data being recorded for different dates. The number of data points for each date changes (normally 1-4). Since I don't know in advance how many data points will be recorded for each date I can't set-up an average for each day. Is there a way to find and list unique dates and then get an average of data for each unique date? (i.e. get an average of the 4 points for 12/01/10, 2 points fo 13th etc) Col 2 col 3 col 4 col 5 col 6 col 7 col 8 Row 7 12/01/10 12/01/10 12/01/10 12/01/10 13/01/10 13/01/10 14/01/10 Row 9 12.3 13.1 12.9 13.0 8.1 8.5 6.5 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging unique values
Try this
O1: = B7 O2: =IF(ISERROR(MATCH(0,COUNTIF(O$1:O1,$B$7:$Z$7&""),0 )),"", INDEX(IF(ISBLANK($B$7:$Z$7),"",$B$7:$Z$7),MATCH(0, COUNTIF(O$1:O1,$B$7:$Z$7&""),0))) Array-enter O2 and copy down as far as you might need P1: =SUMPRODUCT((7:7=O1)*(8:11))/SUMPRODUCT((7:7=O1)*(8:11<"")) Copy P1 down -- HTH Bob "DamienO" wrote in message ... Hi all, I have rows of data being recorded for different dates. The number of data points for each date changes (normally 1-4). Since I don't know in advance how many data points will be recorded for each date I can't set-up an average for each day. Is there a way to find and list unique dates and then get an average of data for each unique date? (i.e. get an average of the 4 points for 12/01/10, 2 points fo 13th etc) Col 2 col 3 col 4 col 5 col 6 col 7 col 8 Row 7 12/01/10 12/01/10 12/01/10 12/01/10 13/01/10 13/01/10 14/01/10 Row 9 12.3 13.1 12.9 13.0 8.1 8.5 6.5 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging unique values
Thanks Bob,
I've been able to get what I want following your reply "DamienO" wrote: Hi all, I have rows of data being recorded for different dates. The number of data points for each date changes (normally 1-4). Since I don't know in advance how many data points will be recorded for each date I can't set-up an average for each day. Is there a way to find and list unique dates and then get an average of data for each unique date? (i.e. get an average of the 4 points for 12/01/10, 2 points fo 13th etc) Col 2 col 3 col 4 col 5 col 6 col 7 col 8 Row 7 12/01/10 12/01/10 12/01/10 12/01/10 13/01/10 13/01/10 14/01/10 Row 9 12.3 13.1 12.9 13.0 8.1 8.5 6.5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging values | Excel Discussion (Misc queries) | |||
averaging less than values | New Users to Excel | |||
averaging less than values | New Users to Excel | |||
Averaging last 25 non zero values. | Excel Discussion (Misc queries) | |||
Averaging selected values | Excel Worksheet Functions |