how to calculate mean, median and mode for grouped data in excel

EduPristine in Excel ShortcutsStatistical Functions in ExcelPeople usually have love-hate relationship with statistics. When you get your formulas right you are in love with it and

how to calculate mean, median and mode for grouped data in excel

EduPristine in Excel Shortcuts

Statistical Functions in Excel

People usually have love-hate relationship with statistics. When you get your formulas right you are in love with it and when your answers go wrong, your feelings take the opposite route. But when you are working on statistics in Excel, things are simpler and less complicated. So today, we bring to you the most used statistical functions of Excel.

Average Function in Excel (Mean)

One of the most used statistical functions in Excel is Average. Calculating the Average in Excel is much simpler than it was in the school. Simply use the Average function and select the range which needs to be averaged. In the example below we want to know the average of the marks obtained by the students so we use =AVERAGE(B2:B12).

Median in Excel

Median is a function which is used to find the middle number in a given range of numbers. When you are finding median manually, you need to sort the data in an ascending order but in Excel, you can simply use the Median function and select the range and you will find your median. We take the same example as above to find the median of marks obtained by students. So we use =MEDIAN(B2:B12).

Mode in Excel

Mode helps you to find out the value that occurs most number of times. When you are working on a large amount of data, this function can be a lot of help. To find the most occurring value in Excel, use the MODE function and select the range you want to find the mode of. In our example below, we use =MODE(B2:B12) and since 2 students have scored 55 we get the answer as 55.

Standard Deviation in Excel

Standard deviation in Excel helps you to understand, how much your values deviate from the Average or Mean that is it tells you that whether your data is somewhere close to the average or fluctuates a lot. If the value received is on the higher side then that means that your data has a lot of fluctuations and vice versa. To calculate standard deviation in excel we use STDEV function. In the same example we shall use the STDEV function so our formula will be =STDEV(B2:B12). Our answer is around 20 which indicates that the marks of the students fluctuates a lot.

As you can see the answers of Average and Standard Deviation contain too many decimals, you can easily get rid of them by using the rounding functions

We hope that your concepts about the statistical functions in Excel have been clear. If you still have any queries then feel free to mention them in the comments box below.

These are some of the commonly used formulas in Excel. There are several courses that train the candidate on using excel for reporting and data management. One of the most in demand skills is Financial Modeling. Financial Models are used by companies to measure the performance of the company, to estimate or forecast future revenue, expenses, risks, etc, to measure the companys growth rate. There are several types of financial models and some of them are as listed below:

  • The Discounted Cash Flow model
  • Comparative Company Analysis model
  • Sum of the parts model
  • Leveraged Buy Out (LBO) model
  • Merger & Acquisition (M&A) model/ merger model
  • Industry specific financial model
  • Option pricing model
  • Corporate finance models

EduPristine provides a Financial Modeling course in collaboration with the BSE Institute. Learn to use advanced excel to create simple and complex Financial Models. Generally, there is no minimum requirement to be eligible for the Financial Modeling course. Due to the complexity and technicality of the course, candidates with a background in Maths, Economics, Statistics find it comfortable to pursue the Financial Modeling course. EduPristine and BSE Institutes collaborative Financial Modeling course can be completed in a duration of 14- 20 days and consists of 70 hours of rigorous training. Financial Modeling Training is available in Classroom format or Live Online classes format to the candidates. The Financial Modeling training consists of practicals and projects on real-world case studies making students industry-ready. The Financial Modeling course is also useful for high profile candidates like Chartered Accountants, Chartered Financial Analysts (CFA), MBAs, etc.

Related links

Rounding functions in Excel

Cell referencing in Excel

Finding and Removing Duplicates in ExcelNext Read: Calendar Heat Maps in Tableau »easy excel tipsExcelExcel Basicsexcel shortcutsexcel tipsFinancial Accountingkeyboard shortcutsquick tipsEduPristine :EduPristine is a member of Adtalem Global Education (NYSE: ATGE), a global education provider headquartered in the United States. Adtalem is a 3 billion dollars (20,000 crores) company that has about 9 institutions and companies with more than 16,000 employees spread across 145 locations. Adtalem takes pride in training 142,000 degree-seeking students all over the world.The organization's purpose is to empower students to achieve their goals, find success and make inspiring contributions to our global community. EduPristine is one of India's leading training providers in Analytics, Accounting, Finance, Healthcare, and Marketing. Founded in 2008, EduPristine has a strong online platform and network of classrooms across India and caters to self-paced learning and online learning, in addition to classroom learning

Related Post

  1. Excel Formulas and shortcuts
  2. Best way to analyse webmaster tools data
  3. 6 Excel Functions that every SEO Expert must knowInterested in this topic?                  Interested in this topic?

All Rights Reserved | View Non-AMP Version

Powered by AMPforWP

Video liên quan