[Solved] Zeros in range [Range Functions]

Discuss the spreadsheet application

[Solved] Zeros in range [Range Functions]

Postby dodu » Wed Dec 12, 2007 12:19 pm

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!
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

Postby TerryE » Wed Dec 12, 2007 12:50 pm

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

Postby dodu » Wed Dec 12, 2007 1:42 pm

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. :idea:
Dodu
dodu
 
Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

Re: Zeros in range

Postby huw » Wed Dec 12, 2007 2:28 pm

How about this?
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

Postby dodu » Wed Dec 12, 2007 2:59 pm

YESSSS ! The second version works even if there is a mix of 0 and blank fields...
:D
Thank you...
Dodu
dodu
 
Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

Re: Zeros in range

Postby TerryE » Wed Dec 12, 2007 9:05 pm

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 :oops:
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

Postby dodu » Thu Dec 13, 2007 12:49 am

:D
Thanks anyway!
Dodu
dodu
 
Posts: 5
Joined: Tue Dec 11, 2007 6:02 pm

Re: Zeros in range

Postby TerryE » Thu Dec 13, 2007 12:30 pm

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

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

Postby huw » Thu Dec 13, 2007 12:35 pm

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

Postby dodu » Thu Dec 13, 2007 4:03 pm

In fact I only have positive whole numbers, so that the solution is perfect for me...
But don't ask me what it does :D ... I am at the level of one plus one is ehmmmm.. ahh. yes... two. :oops:
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

Postby huw » Thu Dec 13, 2007 4:33 pm

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


Return to Calc

Who is online

Users browsing this forum: RPG and 23 guests