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



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

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:24 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average If...

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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, 02:15 AM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default Average If...

Didn't work. Result was #N/A as an array formula. Thanks for trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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, 02:51 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average If...

Here's a small sample file using your posted data that demonstrates this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered) returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't really
numbers like your sample data, or they may look like numbers but they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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:03 PM posted to microsoft.public.excel.worksheet.functions
nadine
external usenet poster
 
Posts: 303
Default Average If...

You are correct. I should have thought of that. They are alpha numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered) returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't really
numbers like your sample data, or they may look like numbers but they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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, 10:14 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average If...

Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A 2:A10)-ROW(A2)+1),B2:B10))

Assumes no empty cells in either range. Empty cells in the Invoice # range
will cause #N/A errors. Empty cells in the amount range could cause an
incorrect result depending on where the empty cells are located.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
You are correct. I should have thought of that. They are alpha numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered)
returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't really
numbers like your sample data, or they may look like numbers but they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for
trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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, 10:43 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Average If...

Of course! Neater than mine, and (like mine) doesn't need to be array
entered. Still suffers from the need for OFFSET() to allow for increasing
range lengths.

Nice one.


"T. Valko" wrote in message
...
Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A 2:A10)-ROW(A2)+1),B2:B10))

Assumes no empty cells in either range. Empty cells in the Invoice # range
will cause #N/A errors. Empty cells in the amount range could cause an
incorrect result depending on where the empty cells are located.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
You are correct. I should have thought of that. They are alpha numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates
this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered)
returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't really
numbers like your sample data, or they may look like numbers but they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for
trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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, 11:03 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average If...

Still suffers from the need for OFFSET() to
allow for increasing range lengths.


If they need dynamic ranges I would create those using InsertNameDefine
rather than building the range in the formula itself: Also, I'd use INDEX
rather than OFFSET if possible.

--
Biff
Microsoft Excel MVP


"Steve Dunn" wrote in message
...
Of course! Neater than mine, and (like mine) doesn't need to be array
entered. Still suffers from the need for OFFSET() to allow for increasing
range lengths.

Nice one.


"T. Valko" wrote in message
...
Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A 2:A10)-ROW(A2)+1),B2:B10))

Assumes no empty cells in either range. Empty cells in the Invoice #
range will cause #N/A errors. Empty cells in the amount range could cause
an incorrect result depending on where the empty cells are located.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
You are correct. I should have thought of that. They are alpha numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates
this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered)
returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't really
numbers like your sample data, or they may look like numbers but
they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for
trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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.


.



.






  #9  
Old April 28th, 2010, 11:38 PM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default Average If...

Curious... why?

"T. Valko" wrote in message
...
Still suffers from the need for OFFSET() to
allow for increasing range lengths.


If they need dynamic ranges I would create those using InsertNameDefine
rather than building the range in the formula itself: Also, I'd use INDEX
rather than OFFSET if possible.

--
Biff
Microsoft Excel MVP


"Steve Dunn" wrote in message
...
Of course! Neater than mine, and (like mine) doesn't need to be array
entered. Still suffers from the need for OFFSET() to allow for
increasing range lengths.

Nice one.


"T. Valko" wrote in message
...
Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A 2:A10)-ROW(A2)+1),B2:B10))

Assumes no empty cells in either range. Empty cells in the Invoice #
range will cause #N/A errors. Empty cells in the amount range could
cause an incorrect result depending on where the empty cells are
located.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
You are correct. I should have thought of that. They are alpha
numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates
this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered)
returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't
really
numbers like your sample data, or they may look like numbers but
they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the
same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for
trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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.


.



.







  #10  
Old April 29th, 2010, 02:04 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default Average If...

Using named dynamic ranges will make the formula shorter and will be easier
to read and understand. And, if using INDEX to define those ranges, the
formula won't be volatile.

--
Biff
Microsoft Excel MVP


"Steve Dunn" wrote in message
...
Curious... why?

"T. Valko" wrote in message
...
Still suffers from the need for OFFSET() to
allow for increasing range lengths.


If they need dynamic ranges I would create those using InsertNameDefine
rather than building the range in the formula itself: Also, I'd use INDEX
rather than OFFSET if possible.

--
Biff
Microsoft Excel MVP


"Steve Dunn" wrote in message
...
Of course! Neater than mine, and (like mine) doesn't need to be array
entered. Still suffers from the need for OFFSET() to allow for
increasing range lengths.

Nice one.


"T. Valko" wrote in message
...
Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A 2:A10)-ROW(A2)+1),B2:B10))

Assumes no empty cells in either range. Empty cells in the Invoice #
range will cause #N/A errors. Empty cells in the amount range could
cause an incorrect result depending on where the empty cells are
located.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
You are correct. I should have thought of that. They are alpha
numeric
sometimes with a dash.

"T. Valko" wrote:

Here's a small sample file using your posted data that demonstrates
this.

zNadine.xls 14kb

http://cjoint.com/?eCdSeTo64Y

As you'll see the formula (which doesn't have to be array entered)
returns
the correct result.

If you're getting an error I suspect that your invoice #s aren't
really
numbers like your sample data, or they may look like numbers but
they're
really TEXT numbers. TEXT numbers and NUMERIC numbers are not the
same.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
Didn't work. Result was #N/A as an array formula. Thanks for
trying.

"T. Valko" wrote:

Try this array formula** :

=AVERAGE(IF(FREQUENCY(A2:A9,A2:A9),B2:B9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Nadine" wrote in message
...
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 07:25 AM.


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