+ Reply to Thread
Results 1 to 4 of 4

Calculating how many 100s are needed to reach target goal

  1. #1
    Registered User
    Join Date
    08-28-2017
    Location
    Albany, New York
    MS-Off Ver
    2016
    Posts
    2

    Calculating how many 100s are needed to reach target goal

    My department does quality checks on my staff's reports and they receive scores on each report. The quality target for everyone is 95%.

    I was wondering if there was a way to calculate how many 100% scores would be needed to raise the quality average from the current amount to the target of 95%.

    I attached some sample data. Hopefully someone can help me. I hope I explained myself clearly enough.


    testdata.PNG
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Calculating how many 100s are needed to reach target goal

    There sure is, but it starts with algebra.

    definition of average: average=sum/count (you should remember this from secondary school if not primary school)
    current form: average=sum/count=9.34/10
    new average: average=sum/count=(9.34+x*1)/(10+x)>=0.95
    Solve inequality for x. At this point, I will assume you know how to solve that for x. If you need an algebra refresher: http://www.purplemath.com/modules/solvelin3.htm
    Translate that formula into Excel to find the number of 100% scores you need to get above 95%.

    If you get stuck, let us know where you get stuck and we'll help you through it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    08-28-2017
    Location
    Albany, New York
    MS-Off Ver
    2016
    Posts
    2

    Re: Calculating how many 100s are needed to reach target goal

    Okay, thanks! I was able to calculate for x (it's 3.2 correct?) I'm having a bit of trouble converting this into a useful formula for Excel. I would like to set it up so if more scores are added, the Excel formula automatically updates how many 100s are needed and I can't seem to wrap my head around that.

    Apologies, I'm not too proficient in Excel!

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,829

    Re: Calculating how many 100s are needed to reach target goal

    3.2 is approximately correct, though x needs to be an integer, so you can't leave it there. 3 100% will yield an average of 94.88%, which is just below 95%. You would need to decide if that is close enough, or if you would prefer to round the result up to 4.

    So far, we really haven't done any Excel, yet. Let's finish the algebra, then the Excel will be easier.

    rather than solve this for the specific case, can you solve this for a more generic case (all variables, no numbers)?

    target average: average=(current sum+1*x)/(current count+x)>=0.95
    solve for x: x=(target average*current count-current sum)/(1-target average)
    You already have cells with current count and target average. If you added a cell to count current sum, it should be straightforward to program that formula into a cell. Then, as I suggested earlier, decide if you need to round up to be sure you are always above 95.000%

+ 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. Moving Monthly Target To Reach Goal
    By jwilso745 in forum Excel General
    Replies: 1
    Last Post: 06-08-2017, 02:45 PM
  2. [SOLVED] Looking for a way to calculate items needed to reach target %
    By UNCDave13 in forum Excel General
    Replies: 13
    Last Post: 01-24-2017, 02:05 AM
  3. Formula to calculate daily sales needed to reach a specific target
    By smsmworld in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-17-2016, 03:27 PM
  4. Calculating value needed to reach a certain % goal
    By Mrjpjones in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2016, 03:38 PM
  5. Finding a Daily Average needed to reach a Monthly goal
    By photoant in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-02-2014, 09:19 PM
  6. [SOLVED] Daily AVG needed to reach monthly goal - Example Attached
    By rufus40444 in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 02-20-2013, 02:47 PM
  7. Excel Formula: Calculating averages to reach a goal?
    By PSUBrian in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-27-2007, 10:46 PM

Tags for this Thread

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