Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,696
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default averageif with 2 other criteria

Try these...

Use cells to hold the criteria.

T3 = active
T4 = mgt

Enter this formula in U3 and copy down to U4:

=AVERAGEIF(R$3:R$1357,T3,Q$3:Q$1357)

For the combined average of active *or* mgt...

Array entered** :

=AVERAGE(IF(ISNUMBER(MATCH(R3:R1357,T3:T4,0)),Q3:Q 1357))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


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



.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
AVERAGEIF T. Valko Excel Worksheet Functions 9 July 15th 09 07:01 PM
Help with Averageif Formula (don't think I should use Averageif) MUmfleet Excel Discussion (Misc queries) 5 April 9th 09 04:53 PM
AVERAGEIF Tung Nguyen Excel Worksheet Functions 3 April 4th 09 01:29 AM
AverageIF with cell refenrence as criteria is not working Bart Geerling Excel Discussion (Misc queries) 4 November 22nd 07 09:36 PM
averageif name Excel Worksheet Functions 1 May 4th 06 05:27 PM


All times are GMT +1. The time now is 10:57 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"