returning the least of a set of numbers...

[optix212]

Okay, So what i want to do is take a series of numbers, located on another sheet, and return the lowest number that is NOT zero. If the number is zero, it should return the penultimate least number.

This is my formula:

Please Login or Register  to view this content.

I'm sorry if this formula is hard to read because I've spaced it out, but I am a programmer and it's insanely hard to read my code any other way as I am used to reading code line-by-line.

For some reason, this method is only returning the value within cell B2 of the DUVAL SPA 5468 worksheet. I have no clue WHY it is, but that's what it is doing.. The value of B2 in the duval spa worksheet is 354.49, but if I go to C2, which is also being checked in my formula, and put a value of 100, which is obviously less than 354.49, it still only returns B2. I don't think this is a logic issue, but it could be..

[carrob]

Is this what you are looking for?

=SMALL('DUVAL SPA 5468'!B2:H2, COUNTIF('DUVAL SPA 5468'!B2:H2, "=0")+1)

[ConneXionLost]

An alternative that includes negative numbers (still not zero):

Please Login or Register  to view this content.

Confirm as an array formula with Ctrl-Shift-Enter.