|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
November 13th, 2009, 06:19 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
averageif with 2 other criteria
Need some help with an averageif calc.
setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in 43:r1357 so averageif looked at other questions in here and cannot find anything close thanks in advance 
|
|
#2
November 13th, 2009, 06:22 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
"v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#3
November 13th, 2009, 06:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
Are you using Excel 2007?
so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 Are those 2 separate requests? Get the average age if column R contains either active *or* mgt. Get the average age if column R contains active. -- Biff Microsoft Excel MVP "v1rt8" wrote in message ... "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#4
November 13th, 2009, 07:19 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
=SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active")
Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#5
November 13th, 2009, 07:41 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
Yes i am, they are two separate requests,
in one cell the avg age of active and mgt in another cell the avg age of active in another cell the avg age of mgt thanks, i should have been more clear "T. Valko" wrote: Are you using Excel 2007? so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 Are those 2 separate requests? Get the average age if column R contains either active *or* mgt. Get the average age if column R contains active. -- Biff Microsoft Excel MVP "v1rt8" wrote in message ... "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance .
|
|
#6
November 13th, 2009, 07:42 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
would this give me the avg age?
"Sean Timmons" wrote: =SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active") Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#7
November 13th, 2009, 07:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
It would give the mean average, which is what AVERAGe in Excel provides. You
are adding together the total of ages, then dividing by the number of people. "v1rt8" wrote: would this give me the avg age? "Sean Timmons" wrote: =SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active") Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#8
November 13th, 2009, 07:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
thank you sir, i appreciate your time and effort
"Sean Timmons" wrote: It would give the mean average, which is what AVERAGe in Excel provides. You are adding together the total of ages, then dividing by the number of people. "v1rt8" wrote: would this give me the avg age? "Sean Timmons" wrote: =SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active") Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#9
November 13th, 2009, 08:02 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
thank you sir for your time and effort
"Sean Timmons" wrote: It would give the mean average, which is what AVERAGe in Excel provides. You are adding together the total of ages, then dividing by the number of people. "v1rt8" wrote: would this give me the avg age? "Sean Timmons" wrote: =SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active") Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
#10
November 13th, 2009, 08:43 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
averageif with 2 other criteria
Not a problem!
"v1rt8" wrote: thank you sir, i appreciate your time and effort "Sean Timmons" wrote: It would give the mean average, which is what AVERAGe in Excel provides. You are adding together the total of ages, then dividing by the number of people. "v1rt8" wrote: would this give me the avg age? "Sean Timmons" wrote: =SUMIF(r3:r1357,"active",q3:q1357)/countif(r3:r1357,"active") Then, =(SUMIF(r3:r1357,"active",q3:q1357)+SUMIF(r3:r1357 ,"mgt",q3:q1357))/(countif(r3:r1357,"active")+countif(r3:r1357,"mgt" )) "v1rt8" wrote: "v1rt8" wrote: Need some help with an averageif calc. setup is as follows: q3:q1357 contains ages r3:r1357 contains text (active), (MGT), (others) so averageif q3:q1357 active and mgt in r3:r1357 so averageif q3:q1357 active in r3:r1357 looked at other questions in here and cannot find anything close thanks in advance
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
