WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Statistics in Excel

Statistics in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel Statistics

In the modern data-driven business world, we have sophisticated software dedicatedly working towards “Statistical Analysis.” Amidst all these modern, technologically advanced excel software is not a bad tool to do your statistical analysis of the data. We can do all kinds of statistical analysis using excel, but you should be an advanced excel user.  In this article, we will show you some of the basic to intermediate level statistics calculations using Excel.

How to use Excel Statistical Functions?

You can download this Statistics Excel Template here – Statistics Excel Template

#1: Find Average Sale per Month

The average rate or average trend is what the decision-makers look at when they want to make some crucial and quick decisions. So finding the average sales, cost, and profit per month is the common task everybody does.

For example, look at the below data of monthly sales value, cost value, and profit value columns in excel.

excel statistical functions Example 1.0

So, by finding the average per month from the whole year, we can actually find what exactly per month numbers.

By using the AVERAGE function, we can find the average values from 12 months, and it boils down to per month on an average.

  • Open the AVERAGE function in the B14 cell.

Example 1.1

  • Select the values from B2 to B13.

excel statistical functions Example 1.2

  • The average value for sales is

Example 1.3

  • Copy and paste the cell B14 to the other two cells as well to get the average cost and profit. The average value for the cost is

excel statistical functions Example 1.4

  • The average value for the profit is

Example 1.5

So, on an average per month, the sale value is USD 25,563, Cost Value is USD 24,550, and Profit Value is USD 1,013.

#2: Find Cumulative Total

Finding the cumulative total is another set of calculations in excel statistics. Cumulative is nothing but adding together all the previous month’s numbers together to find what the current total for the period.

  • For example, look at the below 6 months sales numbers.

excel statistical functions Example 2.0

  • Open SUM function in C2 cells.

Example 2.1

  • Select the cell B2 cell and make the range reference.

excel statistical functions Example 2.2

From the range of cells, make the first part of the cell reference B2 as an absolute reference by pressing the F4 key.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Close the bracket and hit the enter key.

Example 2.3

  • Drag and drop the formula to below one cell.

Statistics in Excel Example 2.4

Now we have got the first two months’ cumulative total, i.e., at the end of the first two months, revenue was $ 53,835. Drag and drop the formula to other remaining cells.

Example 2.5

From this cumulative, we can actually find in which month there was a less revenue increase.

#3: Find Percentage Share

Out of twelve months, you may have got USD 1 Lakh revenue, but maybe in one month, you must have achieved the majority of the revenue, and finding the percentage share of the month actually helps us to find the percentage share of the particular month.

For example, look at the below data of the monthly revenue.

excel statistical functions Example 3

To find the percentage share first, we need to find what the overall 12 months total is, so by applying the SUM function in excel, find the overall sales value.

 Example 3.1

To find the % Share of each month, the formula can be used as follows.

% Share = Current Month Revenue / Overall Revenue

To apply the formula as B2 / B14.

excel statistical functions Example 3.2

The % share for Jan month is

 Example 3.3

Note: Make the overall sales total cell (B14 cell) as an absolute reference because this cell will be a common divisor value across 12 months.

Copy and paste the C2 cell to the below cells as well.

Statistics in Excel Example 3.5

Apply the “Percentage” format to convert the value to percentage values.

 Example 3.7

So, from the above % Share, we can clearly identify “Jun” month has the highest contribution to overall sales v, value, i.e., 11.33% and “May” month has the lowest contribution to overall sales value, i.e., 5.35%.

Statistics in Excel Example 3.8.0

#4: ANOVA Test

Analysis of Variance (ANOVA) is the statistical tool in excel used to find the best available alternative from the lot. For an example, if you are introducing a new four different kind of food to the market and you had given a sample of each food to get the opinion from the public and from the opinion score given by the public by running the ANOVA test, we can choose the best from the lot.

ANOVA is a data analysis tool available to excel under the DATA tab. By default, it is not available. You need to enable it.

Statistics in Excel Example 4.0

Below are the scores of three students from 6 different subjects.

Example 4.1

Click on the “Data Analysis” option under the “Data” tab. It will open up below the “Data Analysis” tab.

excel statistical functions Example 4.2

Scroll up and choose “Anova: Single Factor.”

 Example 4.3

Choose “Input Range” as B1 to D7 and checked in labels in the first row.

Statistics in Excel Example 4.4.0

Select the Output Range as any of the cells in the same worksheet.

 Example 4.5.0

We will have an “ANOVA” analysis-ready.

excel statistical functions Example 4.6.0

Things to Remember

  • All the basic and intermediate statistical analysis possible in excel.
  • We have formulas under the category of “Statistical” formulas.
  • If you are from a statistics background, then it is easy to do some of the fancy and important statistical analyses like “TTEST, ZTEST, and Descriptive Statistics,” etc. in excel.

Recommended Articles

This has been a guide to statistics in excel. Here we discuss how to use excel statistical functions along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • Group Data in Excel
  • Excel Convert Function
  • Median Formula
  • Formula of Arithmetic Mean
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Statistics Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More