Home |
Search |
Today's Posts |
#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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Excel 2003 after downloading Office SP1 | Excel Discussion (Misc queries) | |||
Problems opening Excel files using DFS links | Excel Discussion (Misc queries) | |||
Exel Problems! | Excel Discussion (Misc queries) | |||
Problems pasting images into Excel | Excel Discussion (Misc queries) | |||
Problems with Excel formulas when 2002 upgraded to XP | Excel Worksheet Functions |