Ignore Zeros in Excel Functions MIN() and Others 

I was about to give up then I found your super helpful video. Thanks!!!!

Great tips and techniques! I tinkered myself and came up with these two which also accomplish the goal: =AGGREGATE(15,6,((A2:A7)^2)/(A2:A7),1) =AGGREGATE(15,4,A2:A7,COUNTIF(A2:A7,0)+1) Thanks for the insights and inspiration to experiment! Thumbs up!

Thanks, I had to wade through so many videos to find one which actually explained how to avoid zeros in functions and not just hide the DIV0 error.

Thankyou for having a close up view of the functions, I hate when videos just show the full screen and you're not able to see the functions properly

Deserve a lot more thumb-ups

Excellent tutorial. Thank you.

Amazing trick 👌👌 .. It saved my day !! Thanks a lot.

The ARRAY approach is great. The only problem is when the data size is huge, it may hamper the working of the spread sheet. Thanks for this nicest trick :)

THANK YOU !

best one, working perfect. Thanks

It helped me a lot, thanks.

Thank you

Great. Really useful!

Thank you for the video. How can I add the If function to this relation? For example, I want to calculate only for a selected date written just next to the formula. So when I enter the date I want to bring the result for this range only.

thanks a bunch

Hi thank you for the video, can you please design the same formula with negative values as well. For eg. -1, 0, 1, 2,3,4,5.... now answers should be -1.... Pl explain how to find out minimum value ignoring zero but not negative values .....

Nice. Thanks

Hi! Thanks for the helpful video! Is it possible to use this in PowerPivot when creating a DAX measure? Thanks again!

how to use small function in a sub total function or sub category which changes automatically when given a filter

It didnt work on my ms word 2010.. How can i solve it?

Hi, great video. I am trying to create a formula for MAX that does not count blank cells but does count zeros. I am a novice at this and can't seem to figure it out. Currently, I am using this =IF(MAX(C4:E4)=0,"",MAX(C4:E4)), but if I have scores with zeros across the board, the total cell is blank...any help is much appreciated.

What would you do if you want to ignore zeros in two cells which aren't next to each other? thanks

hi, i hve problems to apply the formula in pivot table, when im trying to summarize value by min, how can i exclude the zeros value...looking forward to your help..

Sir, How to ignore negative signs in finding min value.

your web page is not letting me create an account. is your web page still up?

How to accomplish among from different cells? (Say values in A2, A4, A6 etc)

i was finally able to create an account.

Hi, I have a formula that calculates the number of win draw in a Lotto pool (=SUMPRODUCT(COUNTIF(C5:I5,Draw)) I would like to leave blank cells in place of zeros if there are no wins, can you tell me how to add another "countif" to solve this problem, your help would be much appreciated. regards, Don

Hi I like to nest the above fromula with this formula MIN(IF($A$3:$N$235=B253,$N$3:$N$235)) can u help ? However the following formauls is not wkg MIN(IF($A$3:$N$235=B245,$N$3:$N$235),$N$3:$N$2351)

In Excel 2013, this MIN function is annoying. I have column D with numbers. There will be some numbers less than 0, which I do not want MIN to use. I want MIN to ONLY display the MIN above 0 and ignore any minus number. Logically, I thought that this should work: =MIN(IF(D:D>0,D:D)) In any program coding, that would just work. IF criterial is > 0, the do stuff. But here, it is totally ignore by Excel. If I enter, as a test, -10, then -10 is displayed, even though, it's not above 0! I also tested with a specific range, like D3:D50, but no difference. Does anyone have an idea why this doesn't work and/or a way to get this to work? In this example of actual numbers from my sheet: 09.50 30.00 *01.50* 12.00 -10.50 I want *01.50* to show as the MIN. But -10.50 is displayed. ???? Ideas?

how can do it in vba?

Dear sir, I want average of four number excluding upper and lower number. Example 10, 20, 30, 40 Average is 25

What if A2:A7 is not continuous. ie, A2, B2,C4,G7 . How to do in this case? because SMALL accepts only range as first argumement

How can I apply this formula in the pivot table??