+ Reply to Thread
Results 1 to 6 of 6

Assign a value to a percentage with decimals

  1. #1
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Question Assign a value to a percentage with decimals

    Hi All!

    Boss came to me for a hand creating formula that would look at the adjacent cell's percentage (obtained by adding several cells from other tabs together) and assign a value. I thought it would be simple enough to set up a lookup table but it turns out the percentages can include a decimal and are not only whole number percentages.

    0 is anything <95%
    1 = 95-96%
    2 = 96.1-97%
    3 = 97.1-98%
    4 = 98.1-99%
    5 = 99.1-100%
    So the data would look something like this:
    97.25% 3
    99.59% 5
    98.53% 4

    This should be somewhat simple, but I'm stumped. I've also started an IF/AND nested statement for similar data, but it doesn't completely work (it's assigning anything greater than 7 and 0's incorrectly) . I could modify it for this as well though, I think?
    Please Login or Register  to view this content.
    I think I'm missing something really simple, I'm just not sure what it is and I can't seem to find anything on the forum to point me in the right direction. Any help would be greatly appreciated.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,795

    Re: Assign a value to a percentage with decimals

    Have a look at the VLOOKUP function.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Assign a value to a percentage with decimals

    Try something like this:

    =LOOKUP(A1,{0,0.95,0.961,0.971,0.981,0.991},{0,1,2,3,4,5})

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,125

    Re: Assign a value to a percentage with decimals

    This in A1:B6

    0 0
    95.0% 1
    96.1% 2
    97.1% 3
    98.1$ 4
    99.1% 5


    = VLOOKUP(G1,$A$1:$B$6,2,1)


    G1 is your lookup %
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-16-2012
    Location
    Columbus, OH
    MS-Off Ver
    Excel 2010, Office for Mac 2011
    Posts
    22

    Re: Assign a value to a percentage with decimals

    Thanks all! I've had to amend a few of the statements so they pick up numbers outside of the ranges, otherwise the VLOOKUP is working well.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,125

    Re: Assign a value to a percentage with decimals

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. 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. Rounding 2 decimals to specific 2 decimals
    By danyel191 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2016, 01:41 PM
  2. Replies: 2
    Last Post: 02-13-2015, 07:36 AM
  3. [SOLVED] Multiplying with percentage having decimals not working.
    By smartbuyer in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-17-2013, 06:17 AM
  4. Countif with decimals to give a percentage..HELP!!
    By mrhogan in forum Excel General
    Replies: 10
    Last Post: 11-20-2008, 04:24 AM
  5. Convert numbers with decimals to percentage
    By Wskip49 in forum Excel General
    Replies: 2
    Last Post: 11-11-2008, 06:14 PM
  6. Assign Consistent Letter Value to Grade percentage
    By Keel McDonald in forum Excel General
    Replies: 12
    Last Post: 10-09-2008, 02:48 PM
  7. [SOLVED] assign numbers a value, then get percentage
    By Lt. R in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2006, 01:10 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