+ Reply to Thread
Results 1 to 8 of 8

Percentile formula help

  1. #1
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    Percentile formula help

    Hello,
    I am trying to use formula to automate a scoring card. There are 4 categories each worth 25%. Each category has 5 ratings as below

    "Poor (0% score)"
    "Fair (10% - 50%)"
    Good (50% - 80%)
    "Excellent (80% - 100%)"
    "Outstanding (100% - 120%)"

    For example: If you manually enter a score of 120% in B5 I'd like the total score for that category to be 25% in B6

    let me know if this is not clear. Thanks

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Percentile formula help

    hello like this?

    scoring.xlsx
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: Percentile formula help

    Hi vlady,

    You'll need a small adjustment in your LOOKUP formula:

    Please Login or Register  to view this content.
    Otherwise, any score below 10 will result in an error.
    Last edited by CheshireCat; 08-03-2012 at 03:52 AM.
    Docendo discimus.

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2010
    Posts
    4,332

    Re: Percentile formula help

    missed that one.. thanks CheshireCat, appreciate it.

  5. #5
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Percentile formula help

    Thanks for your speedy replies, I have updated vlady's sheet with an example
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: Percentile formula help

    Could you post an example worksheet with a few figures manually entered with explanation as to why those would be the results?
    If I've been of help, please hit the star

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Percentile formula help

    you also need to clarify your boundaries
    Poor (0% score)"
    "Fair (10% - 50%)"
    Good (50% - 80%)
    "Excellent (80% - 100%)"
    "Outstanding (100% - 120%)"
    you cant have the same say 50% in both fair and good
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  8. #8
    Registered User
    Join Date
    12-30-2011
    Location
    UK
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Percentile formula help

    apologies, from your comments I realize I have not been clear at all. and assume people are thinking what I'm thinking....

    I'll attach another .xls for example.

    To note the top score is 25 but they can overachieve to 30.
    Attached Files Attached Files

+ 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