A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Average Unique



 
 
Thread Tools Display Modes
  #1  
Old April 28th, 2010, 01:41 AM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default Average Unique

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.

  #2  
Old April 28th, 2010, 01:53 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average Unique

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.



  #3  
Old April 28th, 2010, 07:23 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default Average Unique

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

  #4  
Old April 28th, 2010, 03:24 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Average Unique

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.


  #5  
Old April 28th, 2010, 07:09 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default Average Unique

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.


  #6  
Old April 28th, 2010, 07:39 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Average Unique

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.



  #7  
Old April 28th, 2010, 08:39 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default Average Unique

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.



  #8  
Old April 28th, 2010, 09:47 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Average Unique

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.




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.