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.
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.
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.
Method 3 – Scaling Data from 1 to 10 Applying Simple Formula
Steps:
- Enter the following formula in cell F5:
=MIN(C5:C16)
- Press Enter.
- 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.
- Use the Fill Handle to copy the formula in the following cells and get the scaled value from the range C5:C16.
Download the Practice Workbook
You can download the practice workbook from here.
<< Go Back to Scaling Formula in Excel | Excel for Statistics | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
