CODEWITHSUNDEEP
excelexcel-formula
Paul
Paul

Reputation: 1

Excel - use a range of values without the zeros

I am using a column of values that has a mix of positive numbers and zeros. I want to use various functions like MIN, MAX, AVERAGE, etc that calculates the result of just the nonzero numbers. Without making any changes to the data, is there a way to get functions to calculate this?

Ideally, there would be like a MINIF(range,">0") type of function. But I do not believe those exist. I would like to avoid using VBA as well.

Thanks!

Upvotes: 0

Views: 370

Answers (2)

barry houdini
barry houdini

Reputation: 46341

As long as you have Excel 2007 or later you can use AVERAGEIF for averaging without zero, e.g.

=AVERAGEIF(Range,">0")

....but for MIN the normal approach is an array formula as AxGryndr suggests, and that same approach can be used for other functions, although non-array options are available, e.g. for smallest non zero value

=SMALL(Range,COUNTIF(Range,0)+1)

For MAX you can presumably just use a regular MAX function because that will be the same with or without zeroes, i.e.

=MAX(Range)

Upvotes: 1

AxGryndr
AxGryndr

Reputation: 2344

You need to use an array formula like so:

 =MIN(IF($A$1:$A$10<>0,$A$1:$A$10))

Hold CTRL+SHIFT+ENTER in the formula bar to make this an array formula.

You can do the same thing with the MAX and AVERAGE functions.

NOTE: Array formulas are inside { } - this is how you know you did it right.

Upvotes: 1

Related Questions