Are you above or below AVERAGE in Excel?

AVERAGE this word is no new to any of us and it's meaning is known to all of us.  But what does it exactly means? The literal meaning is "a quantity, rating, or the like that represents or approximates an arithmetic mean".  For statistician it has always meant Arithmetic Mean and when used as an adjective it means typical; common; or ordinary. For example "Prakash has AVERAGE excel skills".

Today I am trying to throw some light on AVERAGE function in excel but will also talk about AVERAGEA, AVERAGEIF & AVERAGEIFS.  So be with this post and by the time you finish reading, you'll be equipped with the power of four functions of excel in a matter of few minutes.

AVERAGE function as described in Excel Help is "Returns the average (arithmetic mean) of the arguments." For example, if the range A1:M1 contains numbers, the formula =AVERAGE(A1:M1) returns the average of those numbers.  See the image below explaining this situation:

Things one should know about AVERAGE function are provided arguments can either be numbers or names, ranges, or cell cell references that contains numbers.  Also if a range or cell reference contains text, logical values, or empty cells, those values are ignored; however, cells with negative or zero value are included for calculation of average.

Now what should one do if his data also has negative or zero values and he is looking to get average of only positive values.  Got stuck? Don't worry there is always a solution for every problem, and this riddle can also be solved.  You just need to know the way to approach it.  The formula created by using combination of SUMIF & COUNTIF as =SUMIF(A1:M1,">0")/COUNTIF(A1:M1,">0") can provide us a solution. If you've any other way to solve our problem, please share with our readers in comments at the end of the post.

Now I believe you guys know the AVERAGE function and are ready to go ahead and study other related fuctions I mentioned in the beginning.  First to go is AVERAGEA, this function also provides arithmetic mean but it doesn't ignore logical values and text in arguments.  Empty cell in the range is still ignored by this function as well.  For example in the below you see AVERAGEA(A1:M1) & AVERAGEA(A1:M1,N1) is giving us same result because blank cell N1 is ignored for calculation by function AVERAGEA.

It may also be possible that during your course of routine you would like to know or find average based on single or multiple criteria.  For example you want to calculate the average for numbers above 30 or average for values belonging to a particular category and are above 30 as well.  For situations like these excel has two powerful functions known as AVERAGEIF & AVERAGEIFS for single and multi criteria respectively.

In the image above AVERAGEIF function has been used to calculate average in the range A1:M2 provide that the values are greater than 30. At the same time AVERAGEIFS is being used to satisfy two conditions and calculate average.  Two conditions here are the number should belong to A2 category and should be greater than 30 as well.

So this way today you've added or refreshed functions related to averages in your knowledge base. Let me know about your past or present experiences of using these amazing functions in comments.  "HAPPY LEARNING"