Standardize in Excel – Let’s compare apples with oranges
Can you compare apples with oranges? Generally speaking, the answer is no. However, if we use standard deviation as a measure, then we can. Using standard deviation to compare data with different units is called standardizing. By standardizing data, you are calculating the Z score. Standardized values have no units and hence allow you to compare apples with oranges. In Excel, this is easily done using Standardize.
What is Z Score?
Z scores allow us to compare datasets measured in different units. Z score is how many standard deviations above or below average a data point is.
For any dataset the average Z score will be = 0 and the standard deviation of Z Scores =1. Any Z score above 2 or below -2 is an outlier
The formula for Z score is
(X – mean of data) / Standard deviation of data
Where X is the value you wish to solve for
In Excel the Standardize function will calculate Z Scores. Its syntax is
=STANDARDIZE (x, mean, standard deviation)
Example How to calculate Z Score in Excel using Standardize
You are told the general mean weight of a mango is 10 oz with a standard deviation of 1.75. you are also told in general the mean weight of a banana is 7oz with a standard deviation if 2.2. You buy a banana and a mango and both weigh 9 oz. Which is bigger?
Doing this manually we must standardize the numbers by calculating a Z score to make a comparison using the formula Z= (X – mean of data) / Standard deviation of data
Mango = (9-10) / 1.75 = -0.571
Banana = (9-7) / 2.2 = 0.909
As the Z score for bananas is larger, we can say the banana is larger than the orange.
In Excel this can be solved using the Standardize function. The syntax for Standardize is
=STANDARDIZE (x, mean, standard deviation)
Where we are solving for x given the mean and standard deviation. You can see from the image how these values can be easily plugged into the Standardize function.
Easy right? Great Glad you understand because now it your turn
Earn and Learn Activity – Standardize in Excel – Which is bigger, the apple or the orange?
You buy and apple and an orange, both weigh 12oz. In general, the mean weight for apples is 8oz with a standard deviation of 2 and in general the mean eight for oranges is 10.5oz with a standard deviation of 1.5
Which is bigger, the apple or orange?
To earn STEEM rewards on this post, in the comment section below, answer the following questions
Which is bigger, the apple or the orange?
Share your manual working
Which do you prefer and why, the manual way or using Standardize?
Which do you prefer, apples or oranges?
Next week I will add a video solution for this activity.
Sign up for my newsletter – Don’t worry, I won’t spam. Just useful Excel and Power BI tips and tricks to your inbox
The Excel Club blog has now been powered with STEEM. We are a tokenized website where you can earn while you learn. To earn STEEM tokens you must partake in the practice activity and post your answers, questions or feedback in the comments section below.
I would suggest either of these options for getting a STEEM account
- Purchase an instant account for $3.50 with this link
- Sign up with this link for a free account ( you could be waiting up to 2 weeks)