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" <(E-Mail Removed)> wrote in message
news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
>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 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

"Nadine" <(E-Mail Removed)> wrote in message
news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
> 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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))

HTH
Steve D.



"Nadine" <(E-Mail Removed)> wrote in message
news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
>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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))
>
> HTH
> Steve D.
>
>
>
> "Nadine" <(E-Mail Removed)> wrote in message
> news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:52092F15-5C4E-4DA5-991A-(E-Mail Removed)...
> 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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))
>>
>> HTH
>> Steve D.
>>
>>
>>
>> "Nadine" <(E-Mail Removed)> wrote in message
>> news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
>> >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" <(E-Mail Removed)> wrote in message
> news:52092F15-5C4E-4DA5-991A-(E-Mail Removed)...
> > 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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))
> >>
> >> HTH
> >> Steve D.
> >>
> >>
> >>
> >> "Nadine" <(E-Mail Removed)> wrote in message
> >> news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
> >> >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" <(E-Mail Removed)> wrote in message
news:79339A46-0493-4D04-8FE8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:52092F15-5C4E-4DA5-991A-(E-Mail Removed)...
>> > 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$100,0),MATCH($A$2:$A$100,$A$2:$A$100,0)),$B$2:$B$100))
>> >>
>> >> HTH
>> >> Steve D.
>> >>
>> >>
>> >>
>> >> "Nadine" <(E-Mail Removed)> wrote in message
>> >> news:55956962-01B6-4DDD-B50B-(E-Mail Removed)...
>> >> >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.
>> >> >
>> >>
>>