Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jay
 
Posts: n/a
Default 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   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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



  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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





  #4   Report Post  
Jay
 
Posts: n/a
Default

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






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
Problems with Excel 2003 after downloading Office SP1 Kristy Excel Discussion (Misc queries) 0 February 22nd 05 07:13 PM
Problems opening Excel files using DFS links Byron Excel Discussion (Misc queries) 2 January 2nd 05 12:31 AM
Exel Problems! PW11111 Excel Discussion (Misc queries) 2 December 10th 04 12:34 AM
Problems pasting images into Excel Wazooli Excel Discussion (Misc queries) 2 December 8th 04 12:33 AM
Problems with Excel formulas when 2002 upgraded to XP Kathi McGraw Excel Worksheet Functions 0 November 16th 04 06:27 PM


All times are GMT +1. The time now is 07:44 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"