[How To Find The Average Of A Particular Column In MS Excel
Hien Mondesir
In Microsoft Excel there are a handful of different functions for calculating the average for a set of numeric values. Moreover, there is an instant non-formula way. On this page, you will find a quick overview of all the methods illustrated with examples of use and best practices. All the functions discussed in this tutorial work in any version of Excel 365 through Excel 2007.
In everyday life, the average is a number expressing the typical value in a dataset of data. For example, if a few athletes have run a 100m sprint, you may want to know the average result - i.e. how much time most sprinters are expected to take to complete the race.
(10.5+10.7+11.2)/3 = 10.8
How to get average in Excel without formulasIn Excel worksheets, you do not need to perform manual calculations - powerful Excel functions will do all the work behind the scenes and deliver the result in no time. Before exploring the specialized functions in detail, let's learn a quick and amazingly simple non-formula way.
The result is shown in the image below:If average is not displayed for your dataset, this article explains possible causes and solutions: Why Excel is not showing sum, average or count in the status bar.
How to calculate average manuallyIn math, to find the arithmetic mean of a list of numbers, you need to add up all the values, and then divide the sum by how many numbers there are in the list. In Excel, this can be done using the SUM and COUNT functions, respectively:
As you can see, the result of the formula exactly matches the average value in the status bar.In practice, you will hardly ever need to do a manual average in your worksheets. However, it may be useful to re-check the result of your average formula in case of doubt.
And now, let's take a look at how you can do average in Excel using the functions specially designed for the purpose.
AVERAGE function - calculate an average of numbersYou use the Excel AVERAGE function to get an average of all numbers in the specified cells or ranges.
For more information, please see How to use AVERAGE function in Excel.
AVERAGEA function - average all non-blank cellsThe Excel AVERAGEA function is similar to AVERAGE in that it calculates the arithmetic mean of the values in its arguments. The difference is that AVERAGEA includes all non-empty cells in a calculation, whether they contain numbers, text, logical values, or empty strings returned by other functions.
The image below demonstrates the AVERAGE and AVERAGEA formulas applied to the same list of values and different results they return:AVERAGEIF function - get average with conditionTo get the average of all cells in the specified range that meet a certain condition, use the AVERAGEIF function.
In terms of functionality, the most essential difference is that AVERAGEIF can only handle one condition while AVERAGEIFS one or more criteria. Also, there are a couple of technical differences related to average_range.
Since the Excel AVERAGEIFS function always works with the AND logic (all criteria must be TRUE), you will have to construct your own formula to average cells with the OR logic (any single criterion must be TRUE).
Now, let's see how it works in practice. In the table below, suppose you want to find an average score of two subjects, Biology and Chemistry, which are input in cells F3 and F4. This can be done with the following array formula:
This final array is handed over to the AVERAGE function that calculates an arithmetic mean of numbers ignoring logical values.
How to round average in ExcelWhen calculating an average in Excel, the result is often a number with multiple decimal places. In case you want to display fewer decimal digits or round an average to an integer, use one of the following solutions.
Like the previous method, this changes only the display format. When referring to the average cell in other formulas, the original non-rounded value will be used in all calculations.For full details, please see Round numbers by changing cell format.
Round an average with a formulaTo round the calculated value itself, wrap your average formula in one of the Excel rounding functions.
Thank you for our column.
my question is I want the average temperature for the past 10 days.
The temperatures are in column A rows 2 through 11
The average is calculated in Column B Row 1
On day 11 I want to add the current temperature, preferably in Column A row 2 using INSERT and moving the old data down 1 row.
Using a $ to designate absolute cells does not work, when I use INSERT the formula changes to match the old data.
Hello! Convert the range A1:A11 into a table. Read how to do it here: Excel table: end-to-end tutorial with examples. Use structured references in the formula for calculating the average value. For example:
Hi, I'm trying to get the averages of stats after each basketball game in a season; i.e., free throws, rebounds, etc. I have manually entered the first four 'average' formulas, but wanted to have it done automatically with a formula, so i didn't have to add each new game to the formula manually. Here is a portion of my spreadsheet:
Hello!
I can't check the formula that contains unique references to your workbook worksheets. Each of your formulas returns a value to the cell in which it is written. Calculate the average of these two cells using the AVERAGE function.
Or you can use these formulas as arguments for the AVERAGE function.
Dear Svetlana
I need to easy average fonction. I have table for sold products KG and the rate. I need to average rate of the each products. You can see below a small table but my real table is huge.
Hi,
If by gaps you mean empty cells, then this case is described in detail in the manual above. Use AVERAGEIFS. Also remember that there can only be one counting range for the AVERAGEIF function.
my data is a 5-minutes interval data for the year 2012, i want to get hourly average for each day of the 366days, the complete data has 105408 points, my AVERAGEIFS gives me 27.6 instead of 10.8, the code below is what i used and my excel version is 2007.
I need to average every 5th cell in a row for a workbook that will be recording daily input, it needs to extend on for continued use. I'm using this format and from what I can tell it should work, but averages are puling numbers from every cell not just every 5th cell.
I need a formula to help find the average time between repeat customers. For example, I have the date the customer placed the order in column A, I have the customers name in column B. How do I find the average date between when a customers name is repeated
Hi,
I have a worksheet with many columns right beside each other, and two of the cells in each column that are vertical from one another need to be averaged independently. The two cells in each column that are averaged are dates. I used the formula =AVERAGE(27:27),27:27 is where both dates are placed, but after I pasted my second column beside the first, the second columns average date was affected by the first two dates in the firs column. I guess I told the formula to average ALL the dates together. I want each column to average it's own dates separate without being affected by the other columns. Is there a way to do this? I will be adding a lot more columns in the future so I need a formula that's short. Thanks.
I've got an averages calculation with every other non-adjacent cell
=AVERAGE(B5,D5,F5) etc currently up to SN5 which is 255 numbers. When I try to add the next one in, I keep getting "You've entered too many arguments for this function" but there are no arguments, it's just an average. I've checked to make sure there are no cells in the calculation that shouldn't be there, and everything looks as it should be. Is there a maximum on the number of data cells in an averages calculation? Surely not? Please help!!
I have a daily work in regards with excel, we had an instruction that goes like this "If the sale of this person is below the average of everyone, the commission must be 5%, and 10% if above the average all.
In the column, the result must be either 5% or 10%." I really am having a hard time using the right formula.
Hoping for your positive response.
This function takes the specified column as an argument and finds the average of the values in that column. If you want to find the average of an expression that evaluates to a set of numbers, use the AVERAGEX function instead.
When you average cells, you must keep in mind the difference between an empty cell and a cell that contains the value 0 (zero). When a cell contains 0, it is added to the sum of numbers and the row is counted among the number of rows used as the divisor. However, when a cell contains a blank, the row is not counted.
Whenever there are no rows to aggregate, the function returns a blank. However, if there are rows, but none of them meet the specified criteria, the function returns 0. Excel also returns a zero if no rows are found that meet the conditions.
The AVERAGEA function takes a column as its argument, but otherwise is like the Excel function of the same name. By using the AVERAGEA function, you can calculate a mean on a column that contains empty values.
I'm trying to find a neat formula to collect an average for all values corresponding to an arbitrary year, I have a column with dates and a column of corresponding values. I have a separate indata cell with the relevant year.
Basically, what I want to do is to collect values from all cells that corresponds to the date cells for my selected year and calculate the average of all the collected values. Note that I want to use entire columns as the range expands continuously.
I've tried your formula with some success, although I'm struggling a bit with the year criterion. I've managed to insert a lower limit and would like to complete it with an upper limit as well. Se figure below for formulas.
@Wondimagegn I had a similar problem, which I solved from this video. =XIhbL20jTHc
In the video, the case for sumifs is used. Averageifs is same as sumifs. Beaware of using logical operators when dealing with date data.
I hope it helps!