Supposing your numbers are in cols A and B. Write at c1:
=MAX(CEILING((B1-A1)/(B1-1)*10,1),1)
And drag to the bottom of column C
Answer from Dr. belisarius on Stack OverflowAampe
aampe.com › blog › how-to-normalize-data-in-excel
Aampe - How to Normalize Data in Excel
The formula in Excel would be ... data. Normalizing between 1 and 10, for example, involves applying the formula =1+((value-min)/(max-min))*9....
The Bricks
thebricks.com › resources › guide-how-to-normalize-data-in-excel-between-1-and-10
How to Normalize Data in Excel Between 1 and 10
Since our goal is to normalize data between 1 and 10, we set a = 1 and b = 10. This simplifies our formula to the exact one we will use in Excel:
Videos
Top answer 1 of 4
7
Supposing your numbers are in cols A and B. Write at c1:
=MAX(CEILING((B1-A1)/(B1-1)*10,1),1)
And drag to the bottom of column C
2 of 4
2
You should use the trend function.....I don't know how good you really at solving equations but here is goes
Use trends function in excel.....
trends( Y set of values, x set of values, new x set of values, true)
now specify scale 1 and 10 in Y set of values
for x set of values specify B and 0
and then for new x set of values specify value that you want to normalize
A B
20 120
B =120
since it works on y=mx + c
(x1,y1) = (120,0) and (x2,y2) = (0,10)
any new x that you enter will be normalized on basis of this
so if I enter 20 in new x, then my answer will be 8.333 not 9 (it is wrong)
GeeksforGeeks
geeksforgeeks.org › excel › how-to-normalize-data-in-excel
Normalize Data in Excel - GeeksforGeeks
August 21, 2025 - It is the process of scaling data in such a way that all data points lie in a range of 0 to 1. Thus, this technique, makes it possible to bring all data points to a common scale. The mathematical formula for normalization is given as: ... Xmax and Xmin are the maximum and minimum values in the group of records respectively. Let's have a look at one example to see how can we perform normalization on a sample dataset. Suppose, we have a record of the height of 10 students inside a class as shown below:
Microsoft Community
techcommunity.microsoft.com › microsoft community hub › communities › products › microsoft 365 › excel
Normalization of data - Excel
Hey, i am stuck in normalization of data whose values are between 0-1. The function STDEVA and its associates assumes data to be a population stat which in...
The Bricks
thebricks.com › resources › how-to-normalize-data-in-excel-a-step-by-step-guide
How to Normalize Data in Excel: A Step-by-Step Guide
Excel will automatically apply the formula for each employee. Now, do the same for the "Normalized CSAT" score in cell E2, using the CSAT min/max values: ... Drag this formula down to E11. Your table should now have two new columns with all values between 0 and 1.
Someka
someka.net › home › blog › data analysis › how to normalize data in excel?
How to Normalize Data in Excel ? Methods and Examples
July 24, 2024 - In this blog post, you'll learn about how to normalize data in Excel with step-by-step instructions, charts and real-life examples!
Reddit
reddit.com › r/excel › normalise data between 0-100 based on criteria
r/excel on Reddit: Normalise data between 0-100 based on criteria
March 31, 2022 -
Hi all. I am having difficulties normalising a data set as it is based upon criteria I have such as a 'Category'. Is there a way to normalise a large data set based on criteria using formula?
Please see attached screenshot for a sample of my data and what I am trying to do with it.
https://i.imgur.com/NTEAhPo.png
I am trying to normalise for each individual category, so for A, data value 4 = 0 and data value 24 = 100. Then the same for the rest of the categories.
Thanks
Top answer 1 of 5
2
=(100/(MAXIFS($B$2:$B$15,$A$2:$A$15,$A2)-MINIFS($B$2:$B$15,$A$2:$A$15,$A2)))*($B2-MINIFS($B$2:$B$15,$A$2:$A$15,$A2))
2 of 5
1
u/zstar44 - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
The Bricks
thebricks.com › resources › guide-how-to-normalize-data-in-excel-between-1-and-10-using-chatgpt
How to Normalize Data in Excel Between 1 and 10 using ChatGPT
Identify the Minimum and Maximum Values: Use Excel functions like =MIN() and =MAX() to find these values from your dataset. Apply the Normalization Formula: In a new column, enter the formula for each data point. For instance, if your data is in column A, and you want to normalize it between 1 and 10, your formula might look like this:
Statology
statology.org › home › how to normalize data in excel
How to Normalize Data in Excel
March 30, 2020 - Step 1: Find the mean. First, we will use the =AVERAGE(range of values) function to find the mean of the dataset. Step 2: Find the standard deviation. Next, we will use the =STDEV(range of values) function to find the standard deviation of the ...
Medium
lovespreadsheets.medium.com › normalizing-numerical-values-in-excel-a0eeed827a5b
Normalizing Numerical Values in Excel | by Love Spreadsheets | Medium
September 22, 2022 - The formula for normalizing a value x that comes from a range (y, z) between a range (a, b): X_normalized = (b - a) * [ (x - y) / (z - y) ] + a · Standardization — Changing the original numerical values to fit within a distribution that has ...
The Bricks
thebricks.com › resources › guide-how-to-normalize-data-in-excel-between-1-and-10-using-ai
How to Normalize Data in Excel Between 1 and 10 using AI
Choose Normalization Settings: Configure the plugin to normalize your data between 1 and 10. This usually involves setting the minimum and maximum values. Once the tool has processed your data, you’ll see the normalized values in your designated ...
Reddit
reddit.com › r/excel › how to normalize results.
r/excel on Reddit: How to normalize results.
November 28, 2023 -
I have a dataset for people's opinions on multiple samples, the dataset includes a "1-10" enjoyment scale.
I'd like to normalize the data so that each person's average ranking range is taken into account (example: person A will rank using the whole spectrum of 1-10, but person B will only use the top end of the scale, grading like a teacher with 5 being the lowest passable score)
all results are in 1 table with sample ID, person ID and their provided scores.
Any advice or guidance?
Top answer 1 of 4
1
u/AlwaysImproving10 - Your post was submitted successfully. Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread. Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. Include your Excel version and all other relevant information Failing to follow these steps may result in your post being removed without warning. I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
2 of 4
1
This is more a math question than an Excel one and depends on your intended output. For example, do you want the 5 for person B to be "interpreted" as a 1? If so, you could convert them to ranges (0-100%) with 0% being the lowest score for that person and 100% being the highest score for that person. Assuming scores are in Columns C:F, in G2 (drag across rows and columns): =(C2-MIN($C2:$F2))/(MAX($C2:$F2)-MIN($C2:$F2))
That Excel Site
thatexcelsite.com › home › statistics in excel › how to normalize and standardize data in excel
How to Normalize and Standardize Data in Excel - That Excel Site
November 2, 2022 - Mathematically, we normalize value using the formula below: ... In the example above, you scale each value in a dataset by subtracting the minimum value from the value and dividing it by the difference between the max and minimum values.
Reddit
reddit.com › r/excel › normalizing data in excel?
r/excel on Reddit: Normalizing data in Excel?
November 21, 2015 -
Hi r/excel, so this is also kind of a math question but since it's going to come down to an excel formula I figured this is the right subreddit. So I have a set of 100 numbers that range from a few thousand up to around 25000. I want to express those numbers as a range from 0 to 1. The reason I want to do this is because I want to compare them with another set so I'd like to put them in terms where I'll only have their relationship to each other, if that makes sense.
For example, if I had the numbers 2, 5, 6, 7, and 10, I would want 10 to become 1 (since it's the highest), 7 to become 0.7, 6 to become 0.6, etc. Based on Google searches, I am pretty sure that this is "normalizing" data.
Two questions: is the right mathematical process here? If so, how would I accomplish this in Excel?
Here's a screenshot: http://imgur.com/XGlpeUL
Note: I am generous with ClippyPoints!
Top answer 1 of 3
6
Math professional checking in. I know you already said you found a solution, but just wanted to take the time to clarify for you or anyone else trying to do this type of thing to deepen some understanding. What you described sounds like a percentile. Scoring every item in a data set based on where it falls within the whole range. Excel does have a specific PERCENTRANK() function for this. The thing about percentiles though, is that it doesn't take into account outliers (unusually high or low looking data points) at all - they're evenly distributed. If you have a 100 point data set that is the numbers 1-99, and the 100th point is 250,000 the percentiles will look no different than if that 100th point was actually 100. Using something like this will turn any data set into a uniform distribution, and while it can be useful when wanting to put a single data point into the context of the larger set, it would be utterly useless for comparing two different data sets, so I don't think that's what you're aiming for. The formula that u/speqter suggested is slightly different since it doesn't take the smallest data point into account. It will allow you to see where the patterns are, assuming that the maximum value in the data set is valid or expected. Maybe it is, depending on the nature of what you're actually measuring, but a lot of the time it isn't. Normalizing data (the term in your actual question) specifically refers to what you'll hear in stats classes called a Z-Score. A Z-Score will calculate the number of standard deviations (a way of measuring variability, since I'm kind of in ELI5 mode) a data point is away from the average of the set. What you want to do for that is the formula =(A1-AVERAGE(A:A))/STDEV(A:A) This method of transforming a data set, while not limiting everything in the 0-1 range, will center any data set around 0, and put it on a standard...spread? Basically, just about everything will usually end up falling between -3 and 3. This is absolutely what I'd do if I were trying to compare two data sets since you'll still get the clearest picture of any outliers, and everything is still on the same scale. Screenshot of example (last column, Percent of Max, is the current solution you verified, formulas displayed is for the Z-Score in C2): http://imgur.com/iw0jKVY Again, not trying to discredit any of the other solutions here, just wanted to clarify what each option means and does to data so that anyone trying to accomplish something like this can determine what the best solution is for their particular data set and what they're trying to accomplish.
2 of 3
4
If you wanted the lowest number to be 0, and your highest 1, then (I think) this would be better: =(A1-MIN(A:A))/(MAX(A:A)-MIN(A:A)) For B1, then drag down to duplicate the formula.
Chronicles of Data
chroniclesofdata.com › normalize-data-in-excel
How to Normalize Data in Excel With 1 Easy Formula - Chronicles of Data
June 12, 2023 - To normalize our data in Excel, we will be using the built in STANDARDIZE function, which takes our original values, and brings in both the mean and standard deviation of the data we calculated earlier and outputs a z-score.
Sourcetable
sourcetable.com › how-to-excel › how-to-normalize-the-data-in-excel
How To Normalize Data In Excel
To normalize data, apply the Min-Max scaling formula: (Cell Value - Min Value) / (Max Value - Min Value). This formula will scale the data points between 0 and 1, effectively normalizing your data in Excel.
Published October 30, 2024
Microsoft Community
techcommunity.microsoft.com › microsoft community hub › communities › products › microsoft 365 › excel
Normalization of data | Microsoft Community Hub
Hey, i am stuck in normalization of data whose values are between 0-1. The function STDEVA and its associates assumes data to be a population stat which in...
Microsoft Support
support.microsoft.com › en-us › office › standardize-function-81d66554-2d54-40ec-ba83-6437108ee775
STANDARDIZE function - Microsoft Support
This article describes the formula syntax and usage of the STANDARDIZE function in Microsoft Excel. Returns a normalized value from a distribution characterized by mean and standard_dev. ... X Required. The value you want to normalize. Mean Required. The arithmetic mean of the distribution.
Savant Labs
savantlabs.io › home › a beginner’s guide to standardizing data in excel
STANDARDIZE Function in Excel: A How-to Guide
August 7, 2025 - This means that the values vary by 20 units on average. Now, execute the STANDARDIZE function to standardize each data point. To accomplish this, insert the formula into another column next to the original data.