Average If...


N

Nadine

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

Advertisements

T

T. Valko

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

T. Valko

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

Nadine

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

T. Valko

Ok, for alphnumerics try this array formula** :

=AVERAGE(IF(FREQUENCY(MATCH(A2:A10,A2:A10,0),ROW(A2: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.
 
Ad

Advertisements

S

Steve Dunn

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

T. Valko

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

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

Steve Dunn

Curious... why?

T. Valko said:
If they need dynamic ranges I would create those using Insert>Name>Define
rather than building the range in the formula itself: Also, I'd use INDEX
rather than OFFSET if possible.
 
T

T. Valko

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

Steve Dunn

Can you show how one of our formulae can use INDEX instead of OFFSET in this
instance? I've tried:

$A$2:INDEX($A:$A,COUNTA($A:$A))

instead of:

OFFSET($A$2,,,COUNTA($A:$A)-1)

but the result is a div/0 error.
 
Ad

Advertisements

T

T. Valko

Let's assume the data is in the following ranges:

Invoice = A2:An
Days = B2:Bn

Let's also assume the size of the range will never be greater than 100 rows.

Named ranges...

Invoice
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$100,COUNTA(Sheet1!$A$2:$A$100))

Days
Refers to:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$100,COUNTA(Sheet1!$A$2:$A$100))

Then, the array formula:

=AVERAGE(IF(FREQUENCY(MATCH(Invoice,Invoice,0),ROW(Invoice)-MIN(ROW(Invoice))+1),Days))
 
Ad

Advertisements

S

Steve Dunn

Correction, this does work (in both formulae), and I have no idea why it
gave the div/0 error yesterday...
Oh well, onwards and (possibly) upwards.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top