+ Reply to Thread
Results 1 to 8 of 8

Formula to return score of 0-100 based on column of number data?

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formula to return score of 0-100 based on column of number data?

    I would like to assign a score of 0-100, from a single column of data, based on the highest score, lowest score and average score. The highest score (44.34) would be given 100, the lowest score (39.82) be given 0, and the average score (42.66) 50. All scores in between would be assigned 0-100, accordingly. The data is in column A, and I would like the new score to be listed in column B.
    I'm not sure what formula to use.

    43.56
    43.71
    42.66
    39.82
    42.24
    42.26
    42.43
    43.01
    42.89
    44.00
    41.17
    43.71
    42.31
    41.91
    42.30
    41.35
    42.68
    43.70
    44.16
    43.65
    43.13
    43.00
    40.88
    44.34
    43.26
    40.67
    43.13
    43.14
    43.00
    41.69

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to return score of 0-100 based on column of number data?

    Have you tried the RANK function?

    Something like..

    Please Login or Register  to view this content.
    copied down....will work great for unique values
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Formula to return score of 0-100 based on column of number data?

    Alternatively you could use the Large and Small functions

    =Large(a1:A30,1)
    =Small(A1:A30,1)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    01-22-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula to return score of 0-100 based on column of number data?

    Quote Originally Posted by Ace_XL View Post
    Have you tried the RANK function?

    Something like..

    Please Login or Register  to view this content.
    copied down....will work great for unique values
    Very close to what I'm looking for. That ranks it on a 1-30 scale, but I need it converted to a 0-100 scale, (similar to a curve?).

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Formula to return score of 0-100 based on column of number data?

    theronager,

    Welcome to the forum!
    Attached is an example workbook based on the criteria you described.
    In column A is the list of data you provided in your original post starting in A2.
    In column B is the adjusted score which is derived using this formula in B2 and copied down:
    Please Login or Register  to view this content.

    That formula uses the Reference Table located in K3:L5.
    K3 shows the minimum value listed in the Original Score list:
    Please Login or Register  to view this content.
    L3 shows the desired adjusted score for the minimum value, which is 0 based on your original post.

    K4 shows the average value listed in the Original Score list:
    Please Login or Register  to view this content.
    L4 shows the desired adjusted score for the average value, which is 50 based on your original post.

    K5 shows the maximum value listed in the Original Score list:
    Please Login or Register  to view this content.
    L5 shows the desired adjusted score for the maximum value, which is 100 based on your original post.



    With that table of information, the formula in column B shows the adjusted score accurately for each entry in column A. Is something like that what you're looking for?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

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

  6. #6
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Formula to return score of 0-100 based on column of number data?

    As a side note, the true average value is 42.658666666...
    So even though it shows as 42.66, that is actually a rounded value that is being displayed. This is why the original score 42.66 has an adjusted score of 50.04. 42.66 is not exactly equal to the true average, but it is very close.

  7. #7
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Formula to return score of 0-100 based on column of number data?

    The data is non-linear, so the formula is not straightforward.
    Here is a stab at part of the solution. This works for the max, min and avaerage
    You need to substitute the non-linear formula for the "X"

    =IF(A1=MIN($A$1:$A$30),0,IF(A1=MAX($A$1:$A$30),100,IF(A1=AVERAGE($A$1:$A$30),50,"X")))
    Click on star (*) below if this helps

  8. #8
    Registered User
    Join Date
    01-22-2013
    Location
    Earth
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formula to return score of 0-100 based on column of number data?

    Quote Originally Posted by tigeravatar View Post
    theronager,

    Welcome to the forum!
    Attached is an example workbook based on the criteria you described.
    In column A is the list of data you provided in your original post starting in A2.
    In column B is the adjusted score which is derived using this formula in B2 and copied down:
    Please Login or Register  to view this content.

    That formula uses the Reference Table located in K3:L5.
    K3 shows the minimum value listed in the Original Score list:
    Please Login or Register  to view this content.
    L3 shows the desired adjusted score for the minimum value, which is 0 based on your original post.

    K4 shows the average value listed in the Original Score list:
    Please Login or Register  to view this content.
    L4 shows the desired adjusted score for the average value, which is 50 based on your original post.

    K5 shows the maximum value listed in the Original Score list:
    Please Login or Register  to view this content.
    L5 shows the desired adjusted score for the maximum value, which is 100 based on your original post.



    With that table of information, the formula in column B shows the adjusted score accurately for each entry in column A. Is something like that what you're looking for?
    This did the trick, thank you so much. Can't believe I didn't think to use IF.

+ 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