Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
this is an array formula that must be entered using ctrl+shift+enter instead
of just enter =AVERAGE(IF(J2:J23="lastname",L2:L23)) -- Don Guillett SalesAid Software "Kycajun" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
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" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
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" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
Sorry - one last question. How do I get the result to read 0, if there is
nothing entered for that person? Thanks again. "Don Guillett" wrote: this is an array formula that must be entered using ctrl+shift+enter instead of just enter =AVERAGE(IF(J2:J23="lastname",L2:L23)) -- Don Guillett SalesAid Software "Kycajun" wrote in message ... 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need "average" help
look in help index for ISERR
=if(iserr(formula,0,formula) -- Don Guillett SalesAid Software "Kycajun" wrote in message ... Sorry - one last question. How do I get the result to read 0, if there is nothing entered for that person? Thanks again. "Don Guillett" wrote: this is an array formula that must be entered using ctrl+shift+enter instead of just enter =AVERAGE(IF(J2:J23="lastname",L2:L23)) -- Don Guillett SalesAid Software "Kycajun" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Average" Function in Excel | Excel Discussion (Misc queries) |