Need "average" help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to calculate an average of numbers, based on who owns those
numbers. In summary, D4:D1000 will have a person's last name. M4:m1000 will
have a number entered into it. I want to average M4:M1000 but only using the
numbers that correspond with a single last name. For instance, the last name
Smith appears in d4:d100; I want to average only those numbers associated
with the Smith name. Also, I need to do this without sorting. My results are
on another sheet in the workbook so my information is contained in sheet 1,
but my formula (results) is located in sheet 2. Thanks in advance!
 
this is an array formula that must be entered using ctrl+shift+enter instead
of just enter
=AVERAGE(IF(J2:J23="lastname",L2:L23))
 
Worked beautifully. Thanks so much! I tried the array at one point but my
problem was I was typing 'lastname', instead of "lastname". Thanks again!
 
I think the easiest way would be to use a Pivot Table that would
automatically list each user and the average values per user

But, to use ARRAY FORMULAS*, try this:

On Sheet2:
A1: (name1)
A2: (name2)
etc

B1: =AVERAGE(IF(Sheet1!$D$4:$D$1000=Sheet2!A1,Sheet1!$M$4:$M$1000))
Copy that formula down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP
 
Thanks for the reply. So far Don's solution worked out perfect, but I did
try this one and it works as well. So I will keep it for in the future.
Thanks again.

Ron Coderre said:
I think the easiest way would be to use a Pivot Table that would
automatically list each user and the average values per user

But, to use ARRAY FORMULAS*, try this:

On Sheet2:
A1: (name1)
A2: (name2)
etc

B1: =AVERAGE(IF(Sheet1!$D$4:$D$1000=Sheet2!A1,Sheet1!$M$4:$M$1000))
Copy that formula down as far as needed

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter], instead of just pressing [Enter].

Does that help?
***********
Regards,
Ron

XL2002, WinXP


Kycajun said:
I am trying to calculate an average of numbers, based on who owns those
numbers. In summary, D4:D1000 will have a person's last name. M4:m1000 will
have a number entered into it. I want to average M4:M1000 but only using the
numbers that correspond with a single last name. For instance, the last name
Smith appears in d4:d100; I want to average only those numbers associated
with the Smith name. Also, I need to do this without sorting. My results are
on another sheet in the workbook so my information is contained in sheet 1,
but my formula (results) is located in sheet 2. Thanks in advance!
Click to expand...
Click to expand...
 
Sorry - one last question. How do I get the result to read 0, if there is
nothing entered for that person? Thanks again.
 
You must log in or register to reply here.

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

Similar Threads

average 8
How do I ignore blank cells while averaging the solutions of equat 2
Averages 4
AVERAGE WITH DUPLICATE NUMBERS 5
Average of a list with high and low ignored 3
Excell 2000 IF Statement 1
Multiple criteria question 1
Conditional SUMmations and averages 9

Back
Top