+ Reply to Thread
Results 1 to 5 of 5

If...Then.. Formula needed to recognize employee name and calculate percentage

  1. #1
    Registered User
    Join Date
    01-19-2015
    Location
    Sacramento, CA
    MS-Off Ver
    2007
    Posts
    8

    If...Then.. Formula needed to recognize employee name and calculate percentage

    Column A has list of employee names (some repeating multiple times)
    Column B has yes or no


    A -----B
    Name1 yes
    Name1 yes
    Name no
    Name2 no
    Name2 yes
    Name3 no
    Name3 no
    Name3 yes



    I need to return a percentage (of yes') for each employee.
    I want to match or recognize the name in column 1, then calculate the percentages for all of that employee's yes' or no's.

    Right now I'm using this:
    =COUNTIF(A1:A5,"YES")/COUNTA(A1:A5)
    But this fails to help me, as I count the rows, looking to see how many rows each employee fills (eg, A1:A5) and the actual spreadsheet I'm working on has 3000+ lines...

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: If...Then.. Formula needed to recognize employee name and calculate percentage

    edit after rereading I see what you want is a distinct count.
    Last edited by Sam Capricci; 04-08-2015 at 05:50 PM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,496

    Re: If...Then.. Formula needed to recognize employee name and calculate percentage

    So in your example how do you count name2?

  4. #4
    Forum Contributor
    Join Date
    08-08-2012
    Location
    USA, CA
    MS-Off Ver
    Excel 2007
    Posts
    131

    Re: If...Then.. Formula needed to recognize employee name and calculate percentage

    After reviewing your question, I believe what you are looking for is a % of answers marked "YES" for each employee. You can achieve this with a pivot table.

    1. Highlight A:B, Insert Pivot
    2. Drag column B with your Responses to Column Labels
    3. Drag column A with your Names to Row Labels
    4. Drag Response field from list to Values
    5. Right click one of the numbers in the yes column and Show Value as % of Row Total

    Thanks,
    Newb

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,167

    Re: If...Then.. Formula needed to recognize employee name and calculate percentage

    Hi mikayluh,

    See the attached.
    Attached Files Attached Files
    Last edited by MarvinP; 04-08-2015 at 06:15 PM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to calculate # of hours based on date range and employee ID code
    By r0man3 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-02-2014, 08:04 PM
  2. Calculate Percentage Of Text Occurences per employee
    By MVHC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-24-2013, 09:27 PM
  3. [SOLVED] Formula to calculate unique values for an employee
    By irishguurl in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-16-2013, 03:15 PM
  4. [SOLVED] Formula to calculate if employee is working on each half hour
    By holowugz in forum Excel General
    Replies: 10
    Last Post: 07-16-2012, 08:29 PM
  5. Calculate employee hours for employee evaluation?
    By Triesha in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2006, 10:55 AM

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