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

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

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