Prev: IFNext: 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" 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: IFNext: Macro Help Needed