+ Reply to Thread
Results 1 to 7 of 7

Percentage of target formula

  1. #1
    Registered User
    Join Date
    03-08-2011
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    6

    Percentage of target formula

    Hello all,

    New to the forums.

    I'm in the process of creating a new report and I'm stuck.

    My report consists of a set target in a field, a score and percentage of target.

    e.g Target is 400

    score is 450.

    Percentage is 1.125 of target. (=score/target)

    Now what I would like to do is create a formula that does not go above 100% , but instead work in reverse if you go over the target.

    E.g. Target 400 .. Score was 450 ... percentage of target is 87.5%

    Thanks in advanced.
    Last edited by AndrewSE; 03-08-2011 at 04:56 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    MS 2007, 2010
    Posts
    12,202

    Re: Percentage of target formula

    =IF(score>target, target/score, score/target) Is that what you are looking for?
    Note: if target is 400 and score is 450, score/target is 88.9%, not 87.5%
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    13,775

    Re: Percentage of target formula

    Hi AndrewSE and welcome to the forum
    If A1 = 400 and B1 = 450 then in C1
    Please Login or Register  to view this content.
    should do it for you.
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  4. #4
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: Percentage of target formula

    A little late to the party but perhaps also either of:

    Please Login or Register  to view this content.
    (A1 being Target)

    though the above assumes B1 is not negative
    Last edited by DonkeyOte; 03-08-2011 at 12:44 PM. Reason: missed the all important "y"

  5. #5
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Percentage of target formula

    If you don't want a negative result (when the score gets larger than twice the target), then:
    Please Login or Register  to view this content.
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  6. #6
    Registered User
    Join Date
    03-08-2011
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Percentage of target formula

    Quote Originally Posted by ChemistB View Post
    =IF(score>target, target/score, score/target) Is that what you are looking for?
    Note: if target is 400 and score is 450, score/target is 88.9%, not 87.5%
    That is the one I wanted!

    Thank you all for the assistance !

    This forum is great ! I will for sure learn a thing or two here ! Much appreciated !

  7. #7
    Registered User
    Join Date
    03-08-2011
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Percentage of target formula

    Ok one more question.

    I have a group of sells with this formula representing each month of the year.
    I have data for Jan / Feb... lets say 95% and 90%.. I have 0% for each remaining month.
    95,90,0,0,0,0,0,0,0,0,0,0 Now when I use the normal average formula I get 15.4% since it is including all the fields. Now for the kicker. I still need it to factor in 0% if the percentage of targer is really 0% based on the field with the score.

+ 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