+ Reply to Thread
Results 1 to 3 of 3

Obtaining an average by typing the salesman you want to see and the column to AVG

  1. #1
    Registered User
    Join Date
    05-25-2006
    Posts
    2

    Obtaining an average by typing the salesman you want to see and the column to AVG

    The crux of my problem is that I have an excel sheet keeping track of survey data (switching to access shortly). Right now the following function lets me select a salesman and get an instant average of his sales in the first column (I).

    =AVERAGE(IF($C$2:$C$80=A84,$I$2:$I$80))


    Currently, I type the name of the sales person in A84 and I get a readout on his percentage in column I. Column I contains data from all sales people and its all mixed together.

    John 1
    Steve 1
    John 0
    John 1
    Larry 1
    Sue 1
    Sue 0

    Name: [John] (all I do is type john and hit enter to get...)
    0%

    In my spreadsheet, there are about 10 columns worth of info. I will only need to average their score within the same column, I don't need to relate I to J. I would like to type

    [Q1] in one cell
    [John] in another and get
    0% for the readout.

    The purpose of this is to know at a glance how each sales person is performing in each of 10 areas with only one number displayed at a time.

    Any suggestions?

  2. #2
    Pete_UK
    Guest

    Re: Obtaining an average by typing the salesman you want to see and the column to AVG

    You could try using INDIRECT - along the lines of:

    =AVERAGE(IF($C$2:$C$80=A84,INDIRECT("$"&A83&"$2:$"&A83&"$80")))

    where A83 contains a single letter for the column to be averaged
    (e.g."Q") and A84 contains the name.

    Hope this helps.

    Pete


  3. #3
    Bernard Liengme
    Guest

    Re: Obtaining an average by typing the salesman you want to see and the column to AVG

    My suggestion is Pivot Table
    best wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "furryfishus" <furryfishus.28dtvm_1148592304.0116@excelforum-nospam.com>
    wrote in message
    news:furryfishus.28dtvm_1148592304.0116@excelforum-nospam.com...
    >
    > The crux of my problem is that I have an excel sheet keeping track of
    > survey data (switching to access shortly). Right now the following
    > function lets me select a salesman and get an instant average of his
    > sales in the first column (I).
    >
    > =AVERAGE(IF($C$2:$C$80=A84,$I$2:$I$80))
    >
    >
    > Currently, I type the name of the sales person in A84 and I get a
    > readout on his percentage in column I. Column I contains data from all
    > sales people and its all mixed together.
    >
    > John 1
    > Steve 1
    > John 0
    > John 1
    > Larry 1
    > Sue 1
    > Sue 0
    >
    > Name: [John] (all I do is type john and hit enter to get...)
    > 0%
    >
    > In my spreadsheet, there are about 10 columns worth of info. I will
    > only need to average their score within the same column, I don't need
    > to relate I to J. I would like to type
    >
    > [Q1] in one cell
    > [John] in another and get
    > 0% for the readout.
    >
    > The purpose of this is to know at a glance how each sales person is
    > performing in each of 10 areas with only one number displayed at a
    > time.
    >
    > Any suggestions?
    >
    >
    > --
    > furryfishus
    > ------------------------------------------------------------------------
    > furryfishus's Profile:
    > http://www.excelforum.com/member.php...o&userid=34811
    > View this thread: http://www.excelforum.com/showthread...hreadid=545680
    >




+ 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