ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Use of Standard Deviation in Business – How to calculate Standard Deviation using Microsoft Excel

Updated on April 18, 2013

Introduction

Some of us studied Standard Deviation & some not, and some has not used it so he forgot it.
So let us start from the base.

Note: If you now how to use the formula go directly to the bottom paragraph "How to use for improving business"

The definition: The standard deviation is a measure of how widely values are dispersed from the average value (the mean) (from MS Excel Help).

What does it mean?

Let us say that we have three students have the below marks in four subjects, as we can see all of them have same total 288 of 400, so they have same average as you can see.

Students' marks

Source

So who is better Mary, John or Nike?
We can get the answer by using Standard deviation.

Students' marks & Standard Deviation

Source

As we can see Marry is the best because she always get same mark (I know it is difficult to get that, but I wanted to show when standard deviation value could be Zero), John is the next because he is getting close marks to the average while Nike’s marks is very risky maybe he gets high mark or a very low mark.

The below chart show how much Mary’s marks are stable (just a straight line) John has a little fluctuate line while Nike’s line is a disaster.

Chart showing students' marks

Source

What is the formula of standard Deviation?

Before we speak about the formula we should understand two terms, Population & Sample.
Population: Robert A. Donnelly Jr., Ph.D. says, The term "population" is used in statistics to represent all possible measurements or outcomes that are of interest to us in a particular study."
Examples: Weight of every unit produced of a specific product like sugar or rice, All daily sales of Business.

Sample: Robert A. Donnelly Jr., Ph.D. Says, The term "sample" refers to a portion of the population that is representative of the population from which it was selected.”
Examples: 48 units chosen randomly (Sugar or rice bags or 15 daily Sales randomly selected from all Business Sales).

Now what is the formula?

For standard deviation of a Sample use this one:

Source

While for standard deviation of the entire population, just the denominator will be (n) instead of (n-1).

Source

Where x̅ is the population/sample mean.
And n is the number of figures (it 4 in our previous example).

Steps of calculating standard Deviation

  1. Get the figures in the first column.
  2. Calculate the average of these figures in the second column.
  3. Deduct the every actual figure from the average (Note: sure the total of differences is Zero.
  4. Square every difference (multiply it by itself) in column 3 & write the value in the forth column.
  5. Sum the Figures in column 4.
  6. Divide the result in step 5 by n if you want SD for Entire population or by n-1 if you want ST for Sample.
  7. Now take the square root for the figure you get in step 6, this is Standard deviation.

Below is an example of how to calculate standard deviation, manually or by using direct formulas.
Here I show the Formulas:

Source

Below is same but showing results.

Source

Excel Formulas

Here we can speak about three maybe new formulas for you.

SQRT
Syntax:
SQRT(number) where Number is the number for which you want the square root.

STDEV - Estimates standard deviation based on a sample.
Syntax:
STDEV(number1,number2,...)
Number1, number2, ... are 1 to 255 number arguments corresponding to a sample of a population. You can also use a single array or a reference to an array instead of arguments separated by commas.

STDEVP - Calculates standard deviation based on the entire population given as arguments.
Syntax:
STDEVP(number1,number2,...)

Note:
Here, I am using same data to calculate standard deviation based on a sample or a population for teaching purpose, in real life you chose one othe them depending on the data which you have.
Sure, calculating standard deviation based on a population is more accurate because it includes all the data.

How to use for improving Business

I know that you start feeling bored & tired but now we’ll speack about how to use Standard Deviation in business.

Mostly some risk lay behind fluctuations, fluctuations in Sales, Income, Cost & Quality.etc
So in business world, it is a preferable to reduce fluctuations.
How?

For Example: If we have sales figures item wise, month wise, year wise, salesperson wise, branch wise, group wise etc.

1- We highlight which item, month, year, salesperson, etc has big standard deviation.
2- We analyze the data related properly to find out the real reasons behind these fluctuations.
3- Put well studied procedures for these reason to reduce it to the minimum.

Sometimes we cannot help when fluctuations is related to seasonality of sales, like Ice cream sales, which increase in summer & reduce sharply in winter, but we should work on reducing controllable ones.

Another example: if we are packing rice/sugar, we can take accurate weight for 25 bags randomly, and then calculate the standard deviation for the sample, as much as the SD is less as much the machine is packing well.

Sharing the Knowledge

you could ask any question about Excel in comment, I will do my best to answer it.

If you find this hub useful, share it with others, maybe someone gets some benefit.
Thanks.

The feedback is highly appreciated. Thanks

    0 of 8192 characters used
    Post Comment

    • profile image

      Ashok 

      3 years ago

      HI, I did my MBA. In my MBA program we had statistics for Managers subjects, believe me the person who taught me statistics was so bullshiting, he never explained it correctly the concepts. I am really glad to learn it from you. Please explain the same way about statistical testings and there real life business applications. !!

    • Taleb80 profile imageAUTHOR

      Taleb AlDris 

      5 years ago

      Sure, your mom was better.

      Thanks for your encouraging words.

    • ReneeDC1979 profile image

      Renee' D. Campbell 

      5 years ago from Gaithersburg, Maryland

      Where O where were you in February when I was taking statistics. I could have used your help.. Luckily my mom a retired statistician was able to help. Love the variety in your hubs. Keep hubbing!

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)