Calculate Average Excluding Zero - Excel AVERAGEIF Function

More Excel tips and online courses www.computergaga.com
Calculate average excluding zero in Excel. If there are zeros in a range, the AVERAGE function will include them in its calculation.
To exclude the zeros you need a conditional average formula. By using the AVERAGEIF function in Excel we can create an average excluding zero.
Here are the timings of the video.
00:00 - AVERAGE function on a range including zeroes
01:38 - AVERAGEIF function to average excluding zeroes
03:23 - Closing words
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1

[@aguerojg]

Very interesting tutorial, Computergaga, the explanation is wonderful and straightforward. Thanks for creating the video!

[@edisonleary1711]

thanks I needed this refresher

[@donovancullum6687]

Yeeeeesssss!!!! I have been looking for this for days now! Thank you! Such an easy solution, but finding out how to put it into terms in the search bar was a whole other issue. Thank you Computergaga. You're the man.

[@Computergaga]

Your welcome, thank you Donovan.

[@user-xx7jr4hs4o]

GREAT!. BUNDLE OF THANKS.

[@Computergaga]

You're very welcome.

[@MoonToshRLO]

Thank you for sharing the tutorial.

[@Computergaga]

You’re welcome 😊

[@wadeweisz3976]

Perfect! Just what I was looking for and VERY well explained

[@Computergaga]

Thank you Wade.

[@Viper3870750]

Thanks a lot! I was working on this for hours until I stumbled across your video! This helped me do what I needed to do in a few short minutes after watching!

[@Computergaga]

Your welcome Viper3870750.

[@allanpark6046]

Thank you, your videos are straight to the point.

[@Computergaga]

My pleasure Allan.

[@jeffreycourtright9268]

Your videos are SO HELPFUL!

[@Computergaga]

Thank you Jeffrey. Much appreciated.

[@rodolfinhocs]

perfect!

[@carlosalfonso7504]

Thank you so much, very important lesson!

[@Computergaga]

You're welcome Carlos.

[@Dima_Melnikov]

Спасибо большое!
I'm grateful to you for your lesson

[@Computergaga]

пожалуйста! Thank you.

[@Luis52962]

THANKS FOR SHARING!

[@Computergaga]

You're very welcome Luis.

[@kayuniquebreeze]

THANK YOU FOR THIS

[@Computergaga]

No worries 👍

[@Berghiker]

Thanks. Perfect!

[@Computergaga]

Thanks HowToExcel.

[@Kidzatrocity]

Thanks for sharing this.. You helped me a lot

[@Computergaga]

Great to hear. You're welcome.

[@Dima_Liberty]

Thank you so much!

[@Computergaga]

You're very welcome.

[@peacefulworld9832]

Thanks a lot.

[@Computergaga]

You're welcome, Muhammad.

[@abasitdr7158]

Thank you so much

[@Computergaga]

You're welcome 👍

[@jeffstevenson8027]

thank you kind sir

[@Computergaga]

You're welcome 👍

[@Official_Rocket]

Thanks you

[@Computergaga]

My pleasure. Very welcome.

[@maryjaneclaydon]

brilliant!

[@Computergaga]

Thank you Maryjane.

[@garyprovince]

Thanks! Works in Google Sheets as well.

[@Computergaga]

Great!

[@dariodefendi5379]

One Question: how you do it with different cells? if I use " ; " to select different cells, it gives a kind of error. Thank you

[@sanyasehgal9818]

Thanks! Is there a way to calculate the standard deviation while excluding 0s?

[@Computergaga]

Sure. In this forum post, the IF function is nested inside STDEV for an array formula and to exclude 0's - www.excelforum.com/excel-programming-vba-macros/795202-ignoring-zeros-in-a-stdev-forumula.html

[@zaydarendse2812]

Thanks for that easy solution :) ...should have come straight to your Chanel first

[@Computergaga]

No problem. Thank you Zayd.

[@pablor1278]

Thank you!
Could you please explain why we need the " " though?
Why does excel not understand that the criteria should be >0 without the ">0"?

[@Computergaga]

Thank you. The ifs family of functions - AVERAGEIF, COUNTIFS, SUMIFS, COUNTIF etc all require that the criteria be entered as text, this means within the " ", or as a reference to a cell.

[@yuriydobronravov3090]

Hello, please advise how to use this formula if I need to calculate average not in range but in many separate cells, I see the mistake: "You've entered to many arguments for this formula"

[@Faraaz00723]

Hi there. Can the same be done when counting cells that have values excluding zeros?

[@Computergaga]

I'm not sure what you mean. The video shows an example excluding zeros.

[@Faraaz00723]

Computergaga What I meant was that, is there a way to count the number of cells excluding cells that have zeros and blank cells? The average calculation exclding zeros was amazing and it helped me a lot.

[@Computergaga]

Ah sorry I mis-read your question. You did ask for a counting function, my fault.
There are variations of counting functions. There is a COUNTIF function you could use to excluding blanks and zeros. And COUNTIFS for both. For example, =COUNTIFS(B2:B15,"""",B2:B15,">0")

[@Faraaz00723]

Thank you Computergaga. This trick will help me a lot. Thank you.

[@Computergaga]

You're welcome.

[@harleysebastian3510]

what if i want to average only several particular cells? for ex pupil 7, pupil 9 and pupil 11?

[@Computergaga]

You can use the AVERAGIFS function for whatever condition you require. You will need a way to identify the ones you need. Why pupils 7, 9 and 11. Is it because of their gender, age, region, job title. The reason will be your condition.

[@Dr_Ali.Aljboury]

Hello dear. Please do you have any idea for how to get the ''nonzero'' I need to know how to make it and get number not equal zero. The columns 30 numbers and the numbers some have zero and some have 0.####
So do you know how I get the number of nonzero

[@Computergaga]

Sounds like you want the COUNTIF function to return the number of cells not equal to 0.

[@Dr_Ali.Aljboury]

Thank you very much I found the way since I text you I installed tool in Excel and make it like choose specific cell without zero and then show me the number and the I divide the column with the numbers without zero and then I got the nonzero.

[@Computergaga]

Ok, excellent.

[@Dr_Ali.Aljboury]

Computergaga I have one more question please sorry to bother you. The question is when I collect the data from online I want make this data a good result for example I want used the sperman corrlecation. Do you know how to make the taste. Like I have 30 words with numbers from experts and for me I want optimizatied this values to get a good result. Do you have any idea about this.
I really appreciate your replying to me.

[@punitranjan227]

Hello sir,
I have a question in excel.
Any four number in four different cell that is
10,20,30&40
I want result will be the average of middle of the number it means ignore higher value and lower value.
In this example upper value is 40 and lower value is 10 my expected result will 25.
Please help and suggest the foramula of this steps

[@Computergaga]

To do this accurately, if it is always the top and bottom number, we would need a functions such as INDEX, OFFSET or XLOOKUP to return a dynamic range (one missing the top and bottom value) to the AVERAGE function.
There is a TRIMMEAN function tat works like this, but it uses percentages to exclude, not just top and bottom value.

[@viktorvondoom9119]

What if you want both >0 AND

[@Computergaga]

You could use =AVERAGEIF(B2:B13,"0")

[@viktorvondoom9119]

@@Computergaga Ah thanks!

[@ReekieReels]

If you get a zero in the exam, you should be excluded ;)

[@Computergaga]

😂