[Solved] Zeros in range [Range Functions]

[dodu]

I have different numerical values in a range (A2:AE49) including a few "0". Would like to get the minimum value in this range excluding the "0". I'm sure that there is a very simple solution that I (rank beginner) have overlooked.
Thank you for your suggestions!

[TerryE]

The most elegant that I can thing of offhand is to use a variant on the definition of Average -- that is Sum.../Count....

=SUM(A2:AE49)/(COUNT(A2:AE49)-COUNTIF(A2:AE49;0))

[dodu]

It works if I use it on a small portion of the whole range, otherwise it gives me higher values than the ones I can ferret out by hand (eg. the lowest value I have is 65, but your formula gives me 89). But thank you for putting me onto a thought that I will follow through.
Dodu

[huw]

How about this?

Code: Select all

=LARGE(A2:AE49;RANK(0;A2:AE49)-1)

Edit: that fails if there are no zero values in the range. Try:

Code: Select all

=IF(SMALL(A2:AE49;1)=0;LARGE(A2:AE49;RANK(0;A2:AE49)-1);SMALL(A2:AE49;1))

Keywords for future searchers: find select smallest lowest non-zero value in range

[dodu]

YESSSS ! The second version works even if there is a mix of 0 and blank fields...

Thank you...
Dodu

[TerryE]

Bugger, I misread your Q. I thought that you asked for the average not the minimum. I guess it was a case of RTFQ properly

[dodu]

Thanks anyway!
Dodu

[TerryE]

Yes Dodu, but have you worked out what this function does?

Note that it also works with non-integral and negative values in the range

[huw]

Although with negative numbers included it will return the lowest, not the smallest, non-zero value. i.e. -5 rather than 1 or -1. I'm not sure exactly what you meant by minimum, but if you are only dealing with positive numbers it doesn't matter.

[dodu]

In fact I only have positive whole numbers, so that the solution is perfect for me...
But don't ask me what it does ... I am at the level of one plus one is ehmmmm.. ahh. yes... two.
But I'll tinker around to see if I can understand the workings...
Thx again for taking the time!
Dodu

[huw]

A good way to figure out how a formula works is with the Function Wizard. With the formula cell current, press the button which looks like f(x) with stars floating up from it, on the left of the Input Line.

Explore the Structure by clicking in different places. In conjunction with the descriptions, the highlighting, and the Help button (which you need for "Type" definitions mostly) you can figure out quite a lot.

Note though that "Function result" & "Result" often incorrectly show an error.

Without using the Function Wizard you can also highlight parts of a formula in a cell or the Input Line, and press F9 to get the partial result displyed as a tool-tip.