Prev: IF
Next: Macro Help Needed
From: DamienO on 13 Apr 2010 11:07
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
From: Bob Phillips on 13 Apr 2010 11:39
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" <DamienO(a)discussions.microsoft.com> wrote in message
news:9CE41099-E3CA-43EA-8AE2-959FAB34C4AE(a)microsoft.com...
> 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


From: DamienO on 14 Apr 2010 04:53
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
 | 
Pages: 1
Prev: IF
Next: Macro Help Needed