[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.
Thank you for your suggestions!
Thank you for your suggestions!
Last edited by dodu on Fri Dec 14, 2007 10:57 am, edited 1 time in total.
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))
=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.
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
Re: Zeros in range
How about this?
Edit: that fails if there are no zero values in the range. Try:
Keywords for future searchers: find select smallest lowest non-zero value in range
Code: Select all
=LARGE(A2:AE49;RANK(0;A2:AE49)-1)
Code: Select all
=IF(SMALL(A2:AE49;1)=0;LARGE(A2:AE49;RANK(0;A2:AE49)-1);SMALL(A2:AE49;1))
[Solved] Re: Zeros in range
YESSSS ! The second version works even if there is a mix of 0 and blank fields...
Thank you...
Dodu
Thank you...
Dodu
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.
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
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.
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.
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
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
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.
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.