# daverage problems

1. ## daverage problems

I've got a list of date thus:

date salesperson amount of sale

and a second table that I want to create thus:

salesperson number of sales average sale median sale

I can get the count by uning dcounta, but can't make the daverage function
work in this context. Is there a trick to this? And I have no idea how to
get the median. Any help appreciated.
--
Jay

2. ## Re: daverage problems

Jay,

With your data table in A1:C200 (dates in A, salesperson in B, amount in C),
and your salesperson names in E2, going down column E, in cell F2, enter the
formula

=COUNTIF(\$B\$2:\$B\$200,E2)

In cell G2, array enter (using Ctrl-Shift-Enter)
=AVERAGE(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))
In H2, arrat enter (using Ctrl-Shift-Enter)
=MEDIAN(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))

Copy F2:H2 down to match your salesperson list.

HTH,
Bernie
MS Excel MVP

"Jay" <Jay@discussions.microsoft.com> wrote in message
news:4850B35F-562B-471D-BF19-AA1CD736071B@microsoft.com...
> I've got a list of date thus:
>
> date salesperson amount of sale
>
> and a second table that I want to create thus:
>
> salesperson number of sales average sale median sale
>
>
>
> I can get the count by uning dcounta, but can't make the daverage function
> work in this context. Is there a trick to this? And I have no idea how

to
> get the median. Any help appreciated.
> --
> Jay

3. ## Re: daverage problems

Sorry, I edited my formulas badly:

In cell G2, array enter (using Ctrl-Shift-Enter)
=AVERAGE(IF(\$B\$2:\$B\$200=E2,\$C\$2:\$C\$200))
In H2, array enter (using Ctrl-Shift-Enter)
=MEDIAN(IF(\$B\$2:\$B\$200=E2,\$C\$2:\$C\$200))

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:O5\$iwBrZFHA.2412@TK2MSFTNGP10.phx.gbl...
> Jay,
>
> With your data table in A1:C200 (dates in A, salesperson in B, amount in

C),
> and your salesperson names in E2, going down column E, in cell F2, enter

the
> formula
>
> =COUNTIF(\$B\$2:\$B\$200,E2)
>
> In cell G2, array enter (using Ctrl-Shift-Enter)
> =AVERAGE(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))
> In H2, arrat enter (using Ctrl-Shift-Enter)
> =MEDIAN(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))
>
> Copy F2:H2 down to match your salesperson list.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Jay" <Jay@discussions.microsoft.com> wrote in message
> news:4850B35F-562B-471D-BF19-AA1CD736071B@microsoft.com...
> > I've got a list of date thus:
> >
> > date salesperson amount of sale
> >
> > and a second table that I want to create thus:
> >
> > salesperson number of sales average sale median sale
> >
> >
> >
> > I can get the count by uning dcounta, but can't make the daverage

function
> > work in this context. Is there a trick to this? And I have no idea how

> to
> > get the median. Any help appreciated.
> > --
> > Jay

>
>

4. ## Re: daverage problems

Thanks, Bernie, that's just what I needed. I had suspected that an array
would work, but just didn't know how to apply.
--
Jay

"Bernie Deitrick" wrote:

> Sorry, I edited my formulas badly:
>
> In cell G2, array enter (using Ctrl-Shift-Enter)
> =AVERAGE(IF(\$B\$2:\$B\$200=E2,\$C\$2:\$C\$200))
> In H2, array enter (using Ctrl-Shift-Enter)
> =MEDIAN(IF(\$B\$2:\$B\$200=E2,\$C\$2:\$C\$200))
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:O5\$iwBrZFHA.2412@TK2MSFTNGP10.phx.gbl...
> > Jay,
> >
> > With your data table in A1:C200 (dates in A, salesperson in B, amount in

> C),
> > and your salesperson names in E2, going down column E, in cell F2, enter

> the
> > formula
> >
> > =COUNTIF(\$B\$2:\$B\$200,E2)
> >
> > In cell G2, array enter (using Ctrl-Shift-Enter)
> > =AVERAGE(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))
> > In H2, arrat enter (using Ctrl-Shift-Enter)
> > =MEDIAN(IF(\$B\$2:\$B\$200=E3,\$C\$2:\$C\$200))
> >
> > Copy F2:H2 down to match your salesperson list.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Jay" <Jay@discussions.microsoft.com> wrote in message
> > news:4850B35F-562B-471D-BF19-AA1CD736071B@microsoft.com...
> > > I've got a list of date thus:
> > >
> > > date salesperson amount of sale
> > >
> > > and a second table that I want to create thus:
> > >
> > > salesperson number of sales average sale median sale
> > >
> > >
> > >
> > > I can get the count by uning dcounta, but can't make the daverage

> function
> > > work in this context. Is there a trick to this? And I have no idea how

> > to
> > > get the median. Any help appreciated.
> > > --
> > > Jay

> >
> >

>
>
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1