28.833 is the correct answer, and that is what my formula gives, unless the
range in the formula is outside of the actual range, in which case it gives
#N/A. What were you getting? If you need a variable range, for this you
will need to use OFFSET() as follows:

=AVERAGE(IF(FREQUENCY(MATCH(OFFSET($A$2,,,COUNTA($ A:$A)-1),
OFFSET($A$2,,,COUNTA($A:$A)-1),0),
MATCH(OFFSET($A$2,,,COUNTA($A:$A)-1),
OFFSET($A$2,,,COUNTA($A:$A)-1),0)),
OFFSET($AF$2,,,COUNTA($A:$A)-1)))




"Nadine" wrote in message
...
Here goes:
Col A:Inv # Col AF: #Days to
receive invoice
Rows 2-708 US110-169000 57
Rows 709-813 110-170000 23
Rows 814-817 US110-169000 57
Row 818 US110-169700 21
Row 819 US110-171000 24
Row 820 US110-170400 24
Row 821-824 US110-170220 24

The result should sum of Col AF where Col A is unique (57+23+21+24+24+24)
173 divided by unique count in column A 6 for an average of 28.33

Thanks.

"Steve Dunn" wrote:

Can you give an example of your actual data, and the result you expect to
get?
Obviously use the smallest sample where the formula produces an incorrect
result.


"Nadine" wrote in message
...
Thanks Steve. Unfortunately the result is not the correct number when
just
averaging the individual invoice numbers. It may be getting skewed by
one
of
the invoices being listed much more than the others. Thanks for
trying.

"Steve Dunn" wrote:

Hi Nadine,

if your invoice numbers are always numeric, use Biff's formula from
your
previous post.

If they include characters that are not numeric you will need
something
like:

=AVERAGE(IF(FREQUENCY(MATCH($A$2:$A$100,$A$2:$A$10 0,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100 ))

HTH
Steve D.



"Nadine" wrote in message
...
I posted this question with the title of Average If which was a
misleading
title.

I have a column of invoice numbers and multiple rows of data
pertaining
to
each one. So one invoice number could have 10 rows of data
pertaining
to
it
so the invoice number will be repeated 10 times. Then on each line
is
the
time it took to receive the data so this number will be the same on
all
10
lines. I have hundreds of rows of data but only a handful of
invoice
numbers. I need to find the average of the days based on the unique
count
of
the invoice numbers. I already have my formula for the unique count
but
now
need one for the average when the invoice numbers are not in
contiguous
cells
down the column.

Example:
Col A Col B
Invoice # # Days
12345 11
12345 11
12345 11
98995 15
66438 37
12345 11
98995 15
12345 11

So there are 3 unique invoice numbers. How do I write the formula
to
find
the average number days it took to receive all three? I'm in Excel
2003.