standardize in excel

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.

standardize in excel

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

Learn and Earn Activity Solution

%d bloggers like this: