## [Solved] Zeros in range [Range Functions]

### [Solved] Zeros in range [Range Functions]

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.
Last edited by dodu on Fri Dec 14, 2007 10:57 am, edited 1 time in total.
dodu

Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

### Re: Zeros in range

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))
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer

Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

### Re: Zeros in range

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
dodu

Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

### Re: Zeros in range

Code: Select all   Expand viewCollapse view
`=LARGE(A2:AE49;RANK(0;A2:AE49)-1)`

Edit: that fails if there are no zero values in the range. Try:
Code: Select all   Expand viewCollapse view
`=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
huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

### [Solved] Re: Zeros in range

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

Thank you...
Dodu
dodu

Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

### Re: Zeros in range

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
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer

Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

### [Solved] Re: Zeros in range

Thanks anyway!
Dodu
dodu

Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

### Re: Zeros in range

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
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer

Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

### Re: Zeros in range

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.
huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

### Re: Zeros in range

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
dodu

Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

### Re: Zeros in range

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.
huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm