AVERAGE WITH DUPLICATE NUMBERS
 

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

AVERAGE WITH DUPLICATE NUMBERS
 

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

--
Biff
Microsoft Excel MVP


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

AVERAGE WITH DUPLICATE NUMBERS
 

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


"bhutchinson" wrote:

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

AVERAGE WITH DUPLICATE NUMBERS
 

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,amountran ge)/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.

"bhutchinson" wrote:

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

AVERAGE WITH DUPLICATE NUMBERS
 

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


"מיכאל (מיקי) אבידן" wrote:

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


"bhutchinson" wrote:

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

AVERAGE WITH DUPLICATE NUMBERS
 

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



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