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

1. ## 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. ## 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

3. ## 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)

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

Originally Posted by Ace_XL
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. ## 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?

6. ## 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. ## 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")))

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

Originally Posted by tigeravatar
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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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