+ Reply to Thread
Results 1 to 4 of 4

daverage problems

  1. #1
    Jay
    Guest

    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. #2
    Bernie Deitrick
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Bernie Deitrick
    Guest

    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:[email protected]...
    > 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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. #4
    Jay
    Guest

    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:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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 to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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