Average Unique

[Nadine]

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.

 

[T. Valko]

see you other post

 

[Ashish Mathur]

Hi,

You may try this. Let's say the data is in range C311 (row 3 has the
headings). Select C311 and assign it a name, say trial. Save the file on
the desktop and click on a blank cell.

1. Go to Data > Get External Data > From other sources > From Microsoft
Query
2. Select Excel files and click on OK
3. Navigate tot eh desktop, select the existing Excel file and click on Next
4. Select trial and click on the greater then symbol
5. Click on Next 3 times and on the last screen, select View data or Edit
query in MS Office Excel
6. Click on SQL, delete whatever you see in the box and type the following

Select avg(Days) from
(
SELECT distinct Inv#, Days
FROM try
)

click on OK and you will see the average

7. Go to file > Return Data to MS Office Excel
8. In the import data box, select table and in the white box, select any
blank cell

Hope this works
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Steve Dunn]

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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))

HTH
Steve D.

 

[Nadine]

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]

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]

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]

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)))