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

"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

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" <(E-Mail Removed)> wrote in message
news:AA8800BB-0AA2-45FC-9280-(E-Mail Removed)...
>
>
> "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

=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

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" <(E-Mail Removed)> wrote in message
> news:AA8800BB-0AA2-45FC-9280-(E-Mail Removed)...
> >
> >
> > "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
>
>
> .
>

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

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

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

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

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