AVERAGE WITH DUPLICATE NUMBERS

[bhutchinson]

I need to average a list of data that has duplicate values. john smith
account number 1 has a value of 5. mary smith also on account # 1 has a
value of 5 and is listed sperately. the value of 5 appears in my data twice.
my average is skewed. I want an average of values based on account numbers,
not individuals. i reviewed a few websites. if there is a simple solution
to this i need it. what i found on line is way beyond my excel expertise.

 

[T. Valko]

You'll need to be more specific. Post a small snippet of data and tell us
what result you expect.

 

[מיכ×ל (מיקי) ×בידן]

If I understood you correctly - try this:
http://img189.imageshack.us/img189/6987/nonameu.png
Micky

 

[Paul]

Depending on how much data you have this might be a good use of a pivot
table. You can have it average the value based on the account #. If you
know all of the account #, you could do a sumif of the amounts by account #,
and then do a countif to determine the number of accounts with that #.

=sumif(account#range,account#checkingfor,amountrange)/countif(account#range,account#checkingfor)

This will only work if you know the number of all the accounts, if not then
a pivot table is the way to go.

 

[מיכ×ל (מיקי) ×בידן]

To shorten a little bit:
{=AVERAGE(IF(B2:B9<>B3:B10,C2:C9))}
Micky

 

[Bernie Deitrick]

Bhutch,

Array enter (enter using Ctrl-Shift-Enter) the formula

=SUM(B2:B500/COUNTIF(A2:A500,A2:A500))/SUM(1/COUNTIF(A2:A500,A2:A500))

Where the account numbers are in A2 to A500 and the values to average are in
column B. Note that you cannot have any blanks in those ranges.

HTH,
Bernie
MS Excel MVP