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.

see you other post

--
Biff
Microsoft Excel MVP


"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.

Hi,

You may try this. Let's say the data is in range C3:D11 (row 3 has the
headings). Select C3:D11 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

"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.

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.

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.

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.

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.

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.