Averaging unique values

D

DamienO

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
 
B

Bob Phillips

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top