AverageIf by excluding zeros?

[Artis89]

Hi guys,

I really need your help.

I am stuck in a development on a comparison tool for sales. I have to average Actual Sales from last couple of monts on a specific day. For example I have dont have sales report for May and April sales on working day 8, but I have for june. When averaging sales, it devides june sales by 3, but I should exclude zeros and devide it by 1.

For working day 5 i would have sales for all previous month, so deleting formulas from April and May table would not work.

7-17-2012 12-19-23 PM.png

Values for montly tables are taken from database with formula - =SUMIF(DATABASE!$E:$E,Sales!C$3&Sales!$A$14&Sales!$J$1,DATABASE!$K:$K)
Average is calculated with formula - =AVERAGEIF($B$14:$B$47,$B$9,C$14:C$47)

Could you please advise me on how to make average work by excluding zero values in this case?

Thank you guys a lot!

Regards,
Artis89

[zbor]

Try:

Please Login or Register  to view this content.

[Fotis1991]

Hi

Try


=AVERAGEIFs(C$14:C$47,$B$14:$B$47,$B$9,C$14:C$47,">0")

[Artis89]

Guys, thank you for replies!

Fotis, your suggestion works perfecty! Thanks man and I appreciate the quick response! One question - could you advise me on how to get rid of #div/0! for those months where there are no sales at all? I have to report one new departement and dont have any info for that yet. I am using your formula. Thanks!

Regards,
Artis89

[Ace_XL]

Use

=iferror(AVERAGEIFs(C$14:C$47,$B$14:$B$47,$B$9,C$14:C$47,">0"),"")

[Fotis1991]

Hi

You are welcome.

Try with iferror.

=IFERROR(AVERAGEIFs(C$14:C$47,$B$14:$B$47,$B$9,C$14:C$47,">0"),"")

[Artis89]

You are awesome guys! The best forum with the quickest responses!

Thank you so much! Thanks to you, now I can finish my report template!

[zbor]

Yes, Fotis solution works. sorry, when copy-pasting forgot to chaneg criteria.

[Cutter]

@Artis89
Welcome to the forum. Based on your last post I'll mark your thread as SOLVED. Please remember to do that yourself for your future threads. Thanks.

[Fotis1991]

Nice idea, Cutter!

Congr!

[Artis89]

Cutter,

I am very new to this forum, but future threads Ill do as you suggested.

Thanks!
Artis89

[Cutter]

Thanks Artis89. Enjoy your membership!

And, thank you, too, Fotis.