The average Mean is used to determine the center of a numerical data set. Manually, if you want to find the average Mean, Add all the numerals in the data set and then divide it by the number of values added.
It’s complicated if you calculate mean manually for a huge data set. I have been there a couple of years ago but now, I eased up my work using the simple integrated functions in Microsoft Excel.
With this simple formula, you can find the mean of any numerical data efficiently and swiftly. So,
How to calculate the mean of ungrouped data in Excel?
To calculate the mean of a data set in Excel, type the syntax =AVERAGE, and select the range of the data cells that are to be added in the calculation. Alternatively, Go to the Formulas Tab and Select AutoSum>AVERAGE. Select the Cells that you want to find the mean of and hit Enter.
Example: Suppose, You want to calculate the mean of Column B from the row (2-10). Your Formula will look like this: =AVERAGE(B2:B10). Press Enter to Execute the Formula.
Keep Reading to Find out the Exact Step-By-Step Method to become an Excel Expert.
Calculate Mean of Grouped and Ungrouped Data in Excel
You can calculate the mean of ungrouped data easily in Microsft Excel using the basic formula. However, there ain’t any shortcuts in the case of grouped data. You have to manually calculate the mean. I will show you step-by-step how it’s done. First of all, Let’s find the mean for ungrouped data.
Calculating Mean For Ungrouped Data
There are four simple steps to calculate the mean of any ungrouped data. 1. Enter the data in the spreadsheet 2. Reorganize the Data Set Criteria 3. Select an empty cell to insert formula 4. Close the bracket and press Enter to execute the formula
Here are the steps to calculate mean in Excel:
1. Enter Data in Cells
Enter and Analyze your Data in the spreadsheet. Input a Number on a blank cell and press Enter to move down and the Tab button to move right. You can also design your spreadsheet if you want to present the data to your boss. You can either manually design the file or you can download a template for your work.
2. Reorganize the Data
The next step is reorganizing. Check whether your data contains any non-numerical entries. There are actually four formulas to determine the mean based on numerics, non-numerics, single condition, and multiple conditions. Sort out your data and insert the formula according to need.
3. Insert the Mean Formula
For finding the mean of ungrouped numerical data, type the Syntax =AVERAGE(array of numbers) and press Enter. However, if you have any non-numerical data or text inside the cells that you want to count, type the syntax =AVERAGEA(array of numbers) and press Enter.
NOTE: The difference between the AVERAGE Function and the AVERAGEA function is that Average will only count cells that have numeric entries in them. Whereas AverageA will count all cells including cells that have non-numeric entries.
You can also enter other condition-based formulae as well like; AVERAGEIF (for one condition) and AVERAGEIFS (for multiple conditions).
Worth mentioning, You can execute the mean formula in excel alternatively by clicking on the Formulas Tab and Pressing the AutoSum Icon. There will be a list of Formulas to choose from.
4. Close the Bracket and Execute the Formula
Excel functions will only work if you correctly enter the formula including commas, asterisks, and even brackets. Say, You want to find the mean of a numerical data set from Column H5 to H98. The proper formula will be;
=AVERAGE(H5:H98)
Press Enter and you will get the mean in a blink of an eye.
Calculating Mean For Grouped Data
Let me tell you first, there ain’t any shortcut to calculate the mean for grouped data. You have to calculate them manually.
Say, you were given a class interval and frequency in a data set. First of all, you have to sort the lower and upper limits of the class interval and add the frequencies to get the sum of them.
Then find the midpoint (x) of the upper and lower limit. Example: (Upper Limit + Lower Limit)/2. Now for the function fx, you have to multiply each of the frequencies to its corresponding midpoints. Add to get Σfx.
Now, to determine the mean, you need to remember this formula.
x̄ = Σfx/Σf
Divide the sum of fx with the sum of frequencies to get the average mean for grouped data.
Final Words
Excel formulas make our calculations easier. But to know every formula is tough. I assure you that, by checking our excel posts, you can master any functions with ease.
