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

Originally Posted by Ace_XL

Have you tried the RANK function?

Something like..

=RANK(A1,$A$1:$A$100)

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?).

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:

=IF(A2=$K$4,$L$4,FORECAST(A2,IF(A2<$K$4,$L$3:$L$4,$L$4:$L$5),IF(A2<$K$4,$K$3:$K$4,$K$4:$K$5)))

That formula uses the Reference Table located in K3:L5.
K3 shows the minimum value listed in the Original Score list:

=MIN(A2:A31)

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:

=AVERAGE(A2:A31)

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:

=MAX(A2:A31)

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.