+ Reply to Thread
Results 1 to 13 of 13

Formula to calculate how many more perfect surveys to hit the goal.

  1. #1
    Registered User
    Join Date
    02-03-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2011
    Posts
    3

    Formula to calculate how many more perfect surveys to hit the goal.

    Hi guys,

    Need your help.

    I wish to come up with a Survey calculator(CSAT calculator) for my team. CSAT is basically the satisfied customer response from our customer service surveys.


    Say the CSAT goal is 78%

    And my agent is currently running at 75% CSAT

    With the raw computation of 3 / 4 (CSAT/TOTAL surveys)

    The formula that I need is to compute for the number of CSATs my agent needs to get for him to hit the 78% goal.


    Many thanks!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.68 - (22121100)
    Posts
    7,697

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    try this
    =SUBSTITUTE(TEXT(0.78,"?/?"),"/",":")
    =7:9
    or
    =TEXT(0.78,"?/?")
    will show the results in a divided by format
    =7/9
    Last edited by etaf; 02-03-2013 at 02:58 PM.

  3. #3
    Registered User
    Join Date
    02-03-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    Hi etaf,

    Thank you for the response.

    I maybe doing the formula wrong because I can't seem to get it work. Based from your example, the answer should be 7/9 , which when translated would give us a rounded off percentage of 78%, an exact hit... What I actually need is the 78% as just the minimum target, so basically from the current 3/4 survey of my agent with a 75% translation, he just needs 1 more survey = 4/5 survey and that would give him 80% , hitting the 78% target.


    Regards,

    Carlo

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    Please Login or Register  to view this content.
    If you want a whole-number result,

    =CEILING(B1*(B3-B2)/(1-B3), 1)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    02-03-2013
    Location
    Manila, Philippines
    MS-Off Ver
    Excel 2011
    Posts
    3

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    it's working now!!!

    THANK YOU SO MUCH, SHG!!!

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    You're welcome.

  7. #7
    Registered User
    Join Date
    10-09-2013
    Location
    Rotherham, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    Hi All

    I could do with some help working out a formula for over 60 staff.

    All staff are targeted on a survey, and must achieve a score of 79 from customer satisfaction.

    What I need to know if when agents are not hitting this target how many more perfect score surveys are required.

    An example:
    Agent A has 24 surveys completed, with a score of 79. How many more surveys would agent A need to have completed in order to achieve the minimum of 79?
    Agent B has 5 suveys completed, with a score of 20. How many more surveys would agent B need to have completed in order to achieve the minimum of 79?

    Any help appreciated

    Thank you.

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    Please see post #4.

  9. #9
    Registered User
    Join Date
    11-25-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    Hi all,

    I have been looking at this formula several times, but do not seem to get this one applied to my numbers. Not too certain whether or not CEILING is the formula needed here? Mathematics was never my strongest subject at school :-(
    1. CSAT target = 9 (let's call it B1 for argument sake),
    2. Currently the Employee in question is at 8 (B2),
    3. How many survey(s) is/are needed with a 10 mark each to get back to 9 or above?

    Any help appreciated
    Thanks/.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    It depends on how many surveys comprise the current average of 8.

  11. #11
    Registered User
    Join Date
    11-25-2013
    Location
    Dublin
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    SHG, Survey CSAT is comprised between 0-10, so the number of CSAT with an average of 8 varies week on week.

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    If you don't know how many numbers are in the average, you can't calculate how many "x" scores are needed to get some different average.

  13. #13
    Registered User
    Join Date
    11-12-2016
    Location
    PHILLIPPINES
    MS-Off Ver
    2016
    Posts
    6

    Re: Formula to calculate how many more perfect surveys to hit the goal.

    I need to know what formula to use so that the employee will know how many more Perfect Surveys they needed to generate to achieve a certain goal(86.01 in %).
    Each survey has 5 questions which is rank from 1 (being the lowest) to 5 (being the highest).

    we get the score of the each employee by dividing the total # of 5 they get from each survey from the total # of scores for all surveys.

    EXAMPLE:
    b8 - # of 5s - 25
    e8 - # of non 5s - 5
    i8 - total # of scores - 30
    k8 - # of surveys - 6

    average - 83.33 %

    PLEASE HELP. THANK YOU SO MUCH!!!
    Last edited by jenreindunca; 01-28-2017 at 12:22 PM.

+ 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