Average If

Sorry for reposting this...

I need a formula to figure the average in one column if for a specific
person in another. There are about 2500 entries for each sheet. I need to
know the average with time for Smith, Samsone, etc. without having to sort by
physician.
Thanks.


ER MD WAIT TIME TO SEE PHYSICIAN
smith 0:15
samsone 0:21
terry n/a
terry 0:43
hanson 0:25
smith 0:18
terry n/a
samsone 0:31

=AVERAGE(IF(NAme_Range="Smith",Time_Range))

array entered ctrl + shift & enter

Regards,

Peo Sjoblom

"Denise" wrote:

> Sorry for reposting this...
>
> I need a formula to figure the average in one column if for a specific
> person in another. There are about 2500 entries for each sheet. I need to
> know the average with time for Smith, Samsone, etc. without having to sort by
> physician.
> Thanks.
>
>
> ER MD WAIT TIME TO SEE PHYSICIAN
> smith 0:15
> samsone 0:21
> terry n/a
> terry 0:43
> hanson 0:25
> smith 0:18
> terry n/a
> samsone 0:31
>

I get the error #NUM! when I use this formula.

What am I doing wrong?

"Peo Sjoblom" wrote:

> =AVERAGE(IF(NAme_Range="Smith",Time_Range))
>
> array entered ctrl + shift & enter
>
> Regards,
>
> Peo Sjoblom
>
> "Denise" wrote:
>
> > Sorry for reposting this...
> >
> > I need a formula to figure the average in one column if for a specific
> > person in another. There are about 2500 entries for each sheet. I need to
> > know the average with time for Smith, Samsone, etc. without having to sort by
> > physician.
> > Thanks.
> >
> >
> > ER MD WAIT TIME TO SEE PHYSICIAN
> > smith 0:15
> > samsone 0:21
> > terry n/a
> > terry 0:43
> > hanson 0:25
> > smith 0:18
> > terry n/a
> > samsone 0:31
> >

Did you enter it with ctrl + shift & enter or does any of your ranges include
a #NUM! error or are you using the whole range like A:A? My guess the last
one, if so change the range to something less like A2:A5000..

This formula should yield the same result,

=SUMIF(Name_Range,"Smith",Time_Range)/COUNTIF(Name_Range,"Smith")

also a good idea is to replace the hard coded names (Smith) with a cell
where you would put the names, that way you won't have to edit the formula
again

Regards,

Peo Sjoblom

"Denise" wrote:

> I get the error #NUM! when I use this formula.
>
> What am I doing wrong?
>
> "Peo Sjoblom" wrote:
>
> > =AVERAGE(IF(NAme_Range="Smith",Time_Range))
> >
> > array entered ctrl + shift & enter
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Denise" wrote:
> >
> > > Sorry for reposting this...
> > >
> > > I need a formula to figure the average in one column if for a specific
> > > person in another. There are about 2500 entries for each sheet. I need to
> > > know the average with time for Smith, Samsone, etc. without having to sort by
> > > physician.
> > > Thanks.
> > >
> > >
> > > ER MD WAIT TIME TO SEE PHYSICIAN
> > > smith 0:15
> > > samsone 0:21
> > > terry n/a
> > > terry 0:43
> > > hanson 0:25
> > > smith 0:18
> > > terry n/a
> > > samsone 0:31
> > >

I tried the SUMIF formula and it worked!! THANK YOU!!!

"Peo Sjoblom" wrote:

> Did you enter it with ctrl + shift & enter or does any of your ranges include
> a #NUM! error or are you using the whole range like A:A? My guess the last
> one, if so change the range to something less like A2:A5000..
>
> This formula should yield the same result,
>
> =SUMIF(Name_Range,"Smith",Time_Range)/COUNTIF(Name_Range,"Smith")
>
> also a good idea is to replace the hard coded names (Smith) with a cell
> where you would put the names, that way you won't have to edit the formula
> again
>
> Regards,
>
> Peo Sjoblom
>
> "Denise" wrote:
>
> > I get the error #NUM! when I use this formula.
> >
> > What am I doing wrong?
> >
> > "Peo Sjoblom" wrote:
> >
> > > =AVERAGE(IF(NAme_Range="Smith",Time_Range))
> > >
> > > array entered ctrl + shift & enter
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "Denise" wrote:
> > >
> > > > Sorry for reposting this...
> > > >
> > > > I need a formula to figure the average in one column if for a specific
> > > > person in another. There are about 2500 entries for each sheet. I need to
> > > > know the average with time for Smith, Samsone, etc. without having to sort by
> > > > physician.
> > > > Thanks.
> > > >
> > > >
> > > > ER MD WAIT TIME TO SEE PHYSICIAN
> > > > smith 0:15
> > > > samsone 0:21
> > > > terry n/a
> > > > terry 0:43
> > > > hanson 0:25
> > > > smith 0:18
> > > > terry n/a
> > > > samsone 0:31
> > > >