Excel AVERAGE Function
The Excel AVERAGE Function returns the average (arithmetic mean) of the given numbers in a range or an array. For calculating average with conditions, users use AVERAGEIF or AVERAGEIFS function. The AVERAGE Function can handle up to 255 arguments, including number, reference to cells, ranges, arrays, and constants.
In this section:
- Syntax of AVERAGE Function.
- How to calculate AVERAGE of a number set?
- How to calculate AVERAGE of a given range?
- Dealing with blank cells in AVERAGE.
- AVERAGE with SUM and COUNT.
- Adding a number with the cell value and calculate AVERAGE.
- AVERAGE of top 5 numbers.
- Weighted AVERAGE.
- AVERAGE with Conditions.
- AVERAGE with ROUND numbers to nearest value.
- AVERAGE Last 4 numbers.
Download AVERAGE Function File
1. Syntax
AVERAGE(number1, [number2], …)
where:
- Number1: The number, cell reference, or range of cell reference for which users want the average.
- Number2: More numbers, cell references, or ranges for which users want the average.
Notes:
- The Excel AVERAGE function has one required and other optional arguments, and they can either be numbers or names, ranges, or cell references that contain numbers.
- AVERAGE function ignores logical values, cell containing text, and empty cells.
- Cells with zero are included in the calculation of average.
- Arguments that include error value or text that cannot be translated into numbers cause errors.
- To calculate logical values and text representations of numbers in a reference as part of the calculation, use AVERAGEA function.
- To include criteria in the calculation of average, use AVERAGEIF or AVERAGEIFS function.
Examples:
2. AVERAGE of a Series of Numbers:
To calculate the AVERAGE of a series of numbers, the formula is =AVERAGE(1, 2, 3, 4, 5, 6, 7, 9, 10), which returns the value 5.5.
3. AVERAGE of a Given Range:
To calculate the AVERAGE of a given range, the formula is =AVERAGE(C5:C14), which returns the value of 5.5. Look, here we have used the range of cells.
4. Dealing with blank cells in AVERAGE:
When you are calculating AVERAGE of a range of numbers and some cells are blank, be sure that AVERAGE function ignores blank cells. The formula is the same, and you have to expect that the average will be calculated as =TOTAL/COUNT of cells with non-blank cells.
Let’s take an example. In the column C, Apple, cells C7 and C9 are blanks.
5. AVERAGE with SUM and COUNT:
To manually calculate AVERAGE, we can use the SUM and COUNT function. The formula is =SUM(C5:C14)/COUNT(C5:C14), which returns the value of 5.5.
6. AVERAGE with cell values and Adding a new number:
To calculate AVERAGE by adding a new number with the cell values, the formula is =AVERAGE(C5:C14, 11), which returns the value of 6.
7. AVERAGE of top 5 numbers:
To calculate the average of top 5 numbers, we need to use AVERAGE Function with LARGE function, and the formula is =AVERAGE(LARGE(C5:C14, {1,2,3,4,5})), which returns the average of top 5 numbers from the range C5:C14.
8. AVERAGE of Lowest 5 Numbers:
To calculate the average of lowest 5 numbers, we need to use AVERAGE Function with SMALL function, and the formula is =AVERAGE(SMALL(C5:C14, {1,2,3,4,5})), which returns the average of top 5 numbers from the range C5:C14.
8. Weighted AVERAGE:
We can calculate Weighted Average in two different ways:
Way 1: Using SUM Function:
To calculate weighted average with SUM function, the formula is =SUM(C5*D5,C6*D6,C7*D7,C8*D8)/SUM(C5:C8), which returns the weighted average unit sold and price.
Way 2: By using SUMPRODUCT:
To calculate Weighted Average with SUMPRODUCT, the formula is =SUMPRODUCT(C5:C8,D5:D8)/SUM(C5:C8), which returns the weighted average of unit sold and price.
9. AVERAGE with Conditions.
Single condition:
To calculate AVERAGE with conditions, we can use AVERAGEIF or AVRAGEIFS (with s). For single condition, we use AVERAGEIF and for multiple conditions, use AVERAGEIFS.
In our example, we want to calculate AVERAGE sales of unit where the price less than 50, and the formula is =AVERAGEIF(D5:D10, “<50”, C5:C10), which returns the value of 18.4.
Multiple Conditions:
In our example, we want to calculate AVERAGE of Price where unit sold is more than 4 and total is less than 800. The formula is =AVERAGEIFS(D5:D10, E5:E10, “<800”, C5:C10, “>4”), which returns the value of 21.25.
10. AVERAGE and ROUND numbers to nearest value
To calculate the AVERAGE and round the results to the nearest value, we need to combine the ROUND and AVERAGE formula. The formula we use in our example is =ROUND(AVERAGE(C5:C10),0), which round the number with no digit after decimal point. If you want to add digit after decimal points, write 1,2,3 instead of 0 in the formula.
11. AVERAGE Last 4 numbers
To calculate average of last N values, use the combination of AVERAGE, OFFSET, and COUNT. The formula is =AVERAGE(OFFSET(E4, COUNT(E4:E10),0,-3)), which returns average of last 3 values.
More related readings:
hello there and thank you for your info – I’ve certainly picked up something new from proper here. I did however expertise several technical issues the use of this website, since I skilled to reload the web site a lot of instances previous to I may get it to load properly. I have been pondering in case your web host is OK? No longer that I am complaining, however slow loading instances instances will sometimes impact your placement in google and could harm your high quality ranking if advertising and ***********|advertising|advertising|advertising and *********** with Adwords. Anyway I am including this RSS to my e-mail and could glance out for a lot more of your respective fascinating content. Ensure that you update this again very soon..
Greetings I am so delighted I found your webpage, I really found you by accident, while I was looking on Digg for something else, Regardless I am here now and would just like to say many thanks for a marvelous post and a all round entertaining blog (I also love the theme/design), I don’t have time to read it all at the moment but I have bookmarked it and also added in your RSS feeds, so when I have time I will be back to read a lot more, Please do keep up the excellent b.|
I always spent my half an hour to read this weblog’s posts all the time along with a cup of coffee.|
Great ? I should definitely pronounce, impressed with your site. I had no trouble navigating through all the tabs as well as related info ended up being truly easy to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or something, site theme . a tones way for your client to communicate. Excellent task..
According to my observation, after a property foreclosure home is sold at a sale, it is common with the borrower in order to still have the remaining unpaid debt on the loan. There are many financial institutions who seek to have all expenses and liens paid back by the up coming buyer. However, depending on specific programs, legislation, and state legislation there may be a few loans that are not easily resolved through the shift of financial loans. Therefore, the duty still remains on the client that has had his or her property foreclosed on. Thanks for sharing your ideas on this weblog.
May I simply just say what a relief to discover an individual who really knows what they are talking about on the web. You certainly know how to bring a problem to light and make it important. More and more people really need to read this and understand this side of your story. It’s surprising you are not more popular given that you definitely possess the gift.
Thanks for your posting. Another element is that just being a photographer will involve not only problem in taking award-winning photographs but also hardships in getting the best photographic camera suited to your requirements and most especially challenges in maintaining the caliber of your camera. This is certainly very true and evident for those photography enthusiasts that are directly into capturing the nature’s engaging scenes – the mountains, the particular forests, the particular wild or perhaps the seas. Going to these exciting places undoubtedly requires a digital camera that can meet the wild’s severe environments.
Thanks for your useful article. One other problem is that mesothelioma cancer is generally the result of the breathing of materials from mesothelioma, which is a extremely dangerous material. It is commonly witnessed among workers in the construction industry who may have long contact with asbestos. It can also be caused by residing in asbestos covered buildings for some time of time, Inherited genes plays a huge role, and some individuals are more vulnerable on the risk compared to others.
Hey there! I’m at work surfing around your blog from my new iphone 3gs! Just wanted to say I love reading through your blog and look forward to all your posts! Carry on the excellent work!
One thing is one of the most common incentives for utilizing your credit cards is a cash-back or even rebate present. Generally, you’ll have access to 1-5 back upon various expenses. Depending on the cards, you may get 1 back on most buying, and 5 back again on buying made on convenience stores, gas stations, grocery stores and also ‘member merchants’.
Thanks for discussing your ideas on this blog. Also, a fable regarding the financial institutions intentions whenever talking about foreclosures is that the bank will not have my installments. There is a certain amount of time that the bank requires payments from time to time. If you are too deep inside the hole, they’re going to commonly call that you pay the actual payment entirely. However, i am not saying that they will not take any sort of payments at all. When you and the lender can be capable to work one thing out, the actual foreclosure practice may end. However, when you continue to pass up payments in the new strategy, the foreclosed process can pick up where it was left off.
I have taken notice that in digital camera models, specialized devices help to {focus|concentrate|maintain focus|target|a**** automatically. Those kind of sensors involving some camcorders change in in the area of contrast, while others start using a beam involving infra-red (IR) light, especially in low lighting. Higher specs cameras from time to time use a blend of both systems and may have Face Priority AF where the photographic camera can ‘See’ some sort of face while keeping focused only upon that. Thanks for sharing your ideas on this weblog.
Hey there! I simply want to offer you a big thumbs up for the excellent info you have got right here on this post. I am coming back to your site for more soon.
Hey would you mind sharing which blog platform you’re using? I’m planning to start my own blog soon but I’m having a difficult time choosing between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something unique. P.S Apologies for being off-topic but I had to ask!
Very nice article. I certainly appreciate this website. Continue the good work!
With havin so much content do you ever run into any problems of plagorism or copyright violation? My blog has a lot of completely unique content I’ve either created myself or outsourced but it looks like a lot of it is popping it up all over the web without my authorization. Do you know any techniques to help protect against content from being stolen? I’d definitely appreciate it.
Wonderful site. Plenty of useful information here. I am sending it to a few friends ans also sharing in delicious. And certainly, thank you in your sweat!
Many thanks for this article. I will also like to talk about the fact that it can often be hard if you find yourself in school and simply starting out to establish a long credit history. There are many college students who are merely trying to make it through and have long or beneficial credit history can occasionally be a difficult point to have.
Oh my goodness! a tremendous article dude. Thanks Nevertheless I am experiencing subject with ur rss . Don?t know why Unable to subscribe to it. Is there anybody getting equivalent rss downside? Anyone who knows kindly respond. Thnkx
Saved as a favorite, I love your website!
You actually make it seem really easy along with your presentation but I in finding this topic to be actually something which I think I’d by no means understand. It kind of feels too complex and very extensive for me. I’m looking ahead in your subsequent put up, I will try to get the hold of it!
I was excited to uncover this web site. I want to to thank you for ones time for this particularly wonderful read!! I definitely really liked every bit of it and i also have you book-marked to see new information on your website.
certainly like your web-site however you need to test the spelling on quite a few of your posts. A number of them are rife with spelling problems and I find it very bothersome to inform the reality nevertheless I will definitely come back again.
Have you ever considered about including a little bit more than just your articles? I mean, what you say is important and everything. Nevertheless imagine if you added some great images or videos to give your posts more, “pop”! Your content is excellent but with pics and video clips, this site could undeniably be one of the best in its niche. Very good blog!
Nice i really enjoyed reading your blogs. Keep on posting. Thanks
Good web site! I really love how it is easy on my eyes and the data are well written. I’m wondering how I might be notified when a new post has been made. I’ve subscribed to your RSS which must do the trick! Have a nice day!
Next time I read a blog, Hopefully it doesn’t fail me as much as this particular one. After all, I know it was my choice to read, however I actually thought you’d have something useful to talk about. All I hear is a bunch of moaning about something you could fix if you were not too busy searching for attention.
Hi there! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good results. If you know of any please share. Thank you!
Almanya medyum haluk hoca sizlere 40 yıldır medyumluk hizmeti veriyor, Medyum haluk hocamızın hazırladığı çalışmalar ise bağlama büyüsü ve aşık etme büyüsü , Konularında en iyi sonuç ve kısa sürede yüzde yüz için bizleri tercih ediniz. İletişim: +49 157 59456087
I do love the way you have framed this particular problem and it does supply me personally a lot of fodder for thought. Nevertheless, coming from what I have experienced, I just simply hope when the opinions pile on that men and women remain on point and don’t start on a soap box involving some other news of the day. All the same, thank you for this exceptional point and whilst I do not go along with this in totality, I regard the point of view.
Exceptional post but I was wondering if you could write a litte more on this subject? I’d be very grateful if you could elaborate a little bit further. Thanks!
Almanya medyum haluk hoca sizlere 40 yıldır medyumluk hizmeti veriyor, Medyum haluk hocamızın hazırladığı çalışmalar ise berlin medyum papaz büyüsü, Konularında en iyi sonuç ve kısa sürede yüzde yüz için bizleri tercih ediniz. İletişim: +49 157 59456087
I’ve been browsing online more than three hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my opinion, if all website owners and bloggers made good content as you did, the internet will be much more useful than ever before.
Thanks, I’ve recently been hunting for details about this topic for ages and yours is the best I have found so far.
Hello there, I discovered your site by means of Google while searching for a comparable topic, your site came up, it appears good. I have bookmarked it in my google bookmarks.
Thanks for your posting. Another point is that just being a photographer will involve not only difficulties in catching award-winning photographs but additionally hardships in getting the best photographic camera suited to your requirements and most especially situations in maintaining the quality of your camera. This is certainly very genuine and visible for those photography lovers that are into capturing the particular nature’s fascinating scenes — the mountains, the particular forests, the actual wild and the seas. Visiting these amazing places absolutely requires a dslr camera that can meet the wild’s severe area.
I love it when individuals get together and share thoughts. Great blog, keep it up!
Greetings! Very useful advice within this article! It is the little changes that make the most important changes. Many thanks for sharing!
Thanks for the several tips discussed on this weblog. I have observed that many insurance agencies offer buyers generous discount rates if they decide to insure multiple cars together. A significant variety of households have got several cars or trucks these days, especially those with more mature teenage children still living at home, as well as savings for policies can easily soon begin. So it is a good idea to look for a good deal.
Hello! Do you use Twitter? I’d like to follow you if that would be okay. I’m absolutely enjoying your blog and look forward to new updates.
Wonderful work! This is the type of information that should be shared around the internet. Shame on the search engines for not positioning this post higher! Come on over and visit my website . Thanks =)
I do enjoy the way you have presented this specific challenge and it really does provide us a lot of fodder for consideration. On the other hand, because of everything that I have personally seen, I only wish when other feed-back pack on that people today continue to be on point and in no way embark on a tirade involving the news du jour. All the same, thank you for this superb point and even though I can not necessarily agree with it in totality, I regard the point of view.
fantastic post, very informative. I’m wondering why the other specialists of this sector don’t notice this. You must continue your writing. I’m confident, you have a huge readers’ base already!
I’m really impressed along with your writing abilities and also with the format in your weblog. Is this a paid subject matter or did you customize it yourself? Anyway keep up the excellent quality writing, it is rare to see a nice weblog like this one these days..
Thanks , I have just been searching for information about this topic for ages and yours is the best I’ve came upon so far. However, what in regards to the bottom line? Are you certain in regards to the supply?
It’s hard to come by knowledgeable people about this subject, but you seem like you know what you’re talking about! Thanks
Hey there! I just wanted to ask if you ever have any issues with hackers? My last blog (wordpress) was hacked and I ended up losing months of hard work due to no back up. Do you have any methods to prevent hackers?
Good day! Do you use Twitter? I’d like to follow you if that would be okay. I’m definitely enjoying your blog and look forward to new updates.
Spot on with this write-up, I truly suppose this web site wants rather more consideration. I?ll in all probability be once more to read much more, thanks for that info.
Thank you for another great article. Where else could anybody get that kind of information in such a perfect way of writing? I’ve a presentation next week, and I’m on the look for such info.
Thanks for these tips. One thing I also believe is always that credit cards offering a 0 apr often lure consumers with zero rate of interest, instant acceptance and easy on-line balance transfers, nonetheless beware of the top factor that will probably void your current 0 easy neighborhood annual percentage rate plus throw anybody out into the poor house rapidly.
Hi, I do think this is an excellent site. I stumbledupon it 😉 I will come back once again since I book-marked it. Money and freedom is the best way to change, may you be rich and continue to help other people.
I have realized that online education is getting common because obtaining your college degree online has become a popular selection for many people. A lot of people have definitely not had an opportunity to attend a traditional college or university nevertheless seek the elevated earning possibilities and a better job that a Bachelor Degree offers. Still other individuals might have a degree in one field but would like to pursue one thing they already have an interest in.
whoah this blog is great i love reading your articles. Keep up the good work! You know, lots of people are hunting around for this information, you can aid them greatly.
I just could not leave your website prior to suggesting that I actually enjoyed the usual info an individual supply for your guests? Is gonna be back frequently in order to inspect new posts
Throughout the grand pattern of things you’ll receive an A+ with regard to hard work. Where you actually lost us ended up being on all the details. As people say, details make or break the argument.. And it couldn’t be more correct right here. Having said that, permit me tell you what did do the job. The text can be incredibly persuasive and this is possibly why I am making an effort in order to comment. I do not make it a regular habit of doing that. 2nd, despite the fact that I can see the leaps in reason you come up with, I am not certain of how you seem to unite your ideas which in turn produce the conclusion. For the moment I will, no doubt subscribe to your position but wish in the future you actually connect your dots better.
very nice submit, i actually love this website, keep on it
Hi! This is kind of off topic but I need some guidance from an established blog. Is it hard to set up your own blog? I’m not very techincal but I can figure things out pretty quick. I’m thinking about setting up my own but I’m not sure where to begin. Do you have any points or suggestions? Cheers
Thanks for these pointers. One thing I also believe is the fact that credit cards supplying a 0 interest often entice consumers in zero rate, instant acceptance and easy internet balance transfers, however beware of the top factor that will probably void your own 0 easy streets annual percentage rate and throw anybody out into the terrible house quickly.
A powerful share, I simply given this onto a colleague who was doing somewhat evaluation on this. And he in actual fact purchased me breakfast because I discovered it for him.. smile. So let me reword that: Thnx for the deal with! However yeah Thnkx for spending the time to discuss this, I really feel strongly about it and love reading extra on this topic. If doable, as you turn out to be experience, would you mind updating your weblog with more details? It’s highly helpful for me. Massive thumb up for this blog put up!
Thank you so much for sharing this wonderful post with us.
Hmm it looks like your website ate my first comment (it was super long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I too am an aspiring blog blogger but I’m still new to the whole thing. Do you have any helpful hints for newbie blog writers? I’d really appreciate it.
Very good blog! Do you have any suggestions for aspiring writers? I’m hoping to start my own website soon but I’m a little lost on everything. Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely confused .. Any suggestions? Many thanks!
Hey! I simply want to give an enormous thumbs up for the good info you’ve got here on this post. I will probably be coming again to your weblog for more soon.
Does your blog have a contact page? I’m having problems locating it but, I’d like to shoot you an e-mail. I’ve got some recommendations for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it develop over time.
Thanks for enabling me to attain new thoughts about computer systems. I also contain the belief that certain of the best ways to keep your mobile computer in primary condition has been a hard plastic case, or even shell, that matches over the top of the computer. A lot of these protective gear will be model unique since they are manufactured to fit perfectly above the natural covering. You can buy them directly from the vendor, or from third party places if they are for your laptop, however its not all laptop will have a cover on the market. Once more, thanks for your tips.
I do not even know how I ended up right here, but I believed this publish was once great. I do not recognize who you are but certainly you are going to a well-known blogger when you are not already 😉 Cheers!
Thank you so much for sharing this wonderful post with us.
Can I simply say what a comfort to find an individual who really understands what they’re discussing on the net. You certainly realize how to bring an issue to light and make it important. More and more people have to look at this and understand this side of the story. I was surprised that you’re not more popular because you most certainly have the gift.
Hey there would you mind letting me know which hosting company you’re working with? I’ve loaded your blog in 3 completely different internet browsers and I must say this blog loads a lot quicker then most. Can you suggest a good internet hosting provider at a fair price? Many thanks, I appreciate it!