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

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

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

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