How to Scale Data from 1 to 10 in Excel (3 Quick Methods)

Method 1 – Using INT, CEILING and MAX Functions to Scale Data

In the first method, we have taken exam marks for a few students and will scale the obtained marks on a scale of 10. We will use Excel’s INT, CEILING, and MAX functions.

excel scale data 1 to 10

Steps:

Enter the following formula in Cell D5:

=INT(CEILING(C5,MAX($C$5:$C$16)/10)*10/MAX($C$5:$C$16))

Press Enter.

We will see the scaled value of Cell C5 in Cell D5.

How Does the Formula Work?

MAX($C$5:$C$16)/10: This part divides the maximum value of range $C$5:$C$16 by 10.
CEILING(C5,MAX($C$5:$C$16)/10: Here, the value of C5 is expressed as the immediate upper multiple of output from MAX($C$5:$C$16)/10, which is multiplied by 10 and divided by the maximum value of range $C$5:$C$16.
Lastly, the INT function takes the output as an argument and gives the respective integer value, discarding the fractional part.

Use the Fill Handle to get the scaled values in the following cells.

excel scale data 1 to 10 result

Read More: How to Do Data Scaling in Excel

Method 2 – Utilizing MIN and MAX Functions for Scaling Data from 1 to 10

Steps:

Enter the following formula in cell F5:

=INDEX($B$5:$D$14,MATCH(1,($B$5:$B$14<=E5)*($C$5:$C$14>=E5),0),3)

Press Enter, and you will see the scaled value in cell F5.

How Does the Formula Work?

MATCH(1,($B$5:$B$14<=E5)*($C$5:$C$14>=E5),0): This part finds the row number of the range from Min & Max column where the value of Cell E5 belongs.
The INDEX function takes the row number and gives the output from column 3 for that row number.

Use the AutoFill feature to get the rest scaled value in the following cells.

excel scale data 1 to 10 result

Method 3 – Scaling Data from 1 to 10 Applying Simple Formula

Steps:

Enter the following formula in cell F5:

=MIN(C5:C16)

Press Enter.

Scale Data from 1 to 10 Applying Simple Formula

Enter the following formula in cell F7 to get the maximum value of the range C5:C16:

=MAX(C5:C16)

Determine the difference between the maximum and minimum values in cell F9 using the following formula:

=F7-F5

Press Enter.

Now, we will use the previously determined maximum value and minimum value and their difference to determine the scaled value of the Marks column.

Enter the following formula in cell D5:

=ROUND((C5-$F$5)/$F$9*10,0)

Press Enter. We will see the scaled value of cell C5 there.

Note: We applied the formula Rank = (Original Value-Minimum value)/Difference of Maximum and Minimum Value*10. Later on, the ROUND function rounds the output number, discarding the fractional value.