Finding the smallest number and >0 in a set of cells

[Gustavo]

Hi all.

I've a master shape that has about 50 Scratch cells that store dynamic numerical data, and I've to find the smallest number of them all. Since always some cells store a 0 value or a "NoFormula", when I use the function MIN(Scratch.A1,Scratch.A2,...), this return a 0, and this is not useful. How can I find the smallest number different to 0, with shapesheet functions? Any idea would be appreciated. Thanks.

Best regards.

[wapperdude]

Shape sheet only solution or code solution OK?

[Gustavo]

Hi wapperdude, thanks for your reply.

I prefer this to be a shapesheet functions solution, 'cause the shape with the Scratch cells will be nested into another shape, and this will overcomplicate a VBA solution.

[wapperdude]

One more question, you are only interested in the smallest value and don't care what cell contains it, yes?

[Gustavo]

for now, only the value, its my minimal functionality. I'll deal with the cell later.

[Gustavo]

Thank you for your interest wapperdude. I've come across a solution: First I tried:

Code

=MIN(IF(Scratch.A1>0,Scratch.A1,""),IF(Scratch.A2>0,Scratch.A2,""),IF(Scratch.A3>0,Scratch.A3,""),IF(Scratch.A4>0,Scratch.A4,""),...IF(Scratch.An>0,Scratch.An,""))

This will produce the less value number > 0, as this disregard any cell with a 0 number, and with the "No Formula" that produces an unintended logical 0 in the MIN function. Then I realized that sometimes there is a cell with 0 value that IS intended, so in this case that last formula produces an unexpected result. For that case, I've come across with the function FORMULAEXISTS, that produces a logical 1 when the cell has a different value than a "No Formula". So the calculation I came up:

Code

=MIN(IF(FORMULAEXISTS(Scratch.A1),Scratch.A1,""),IF(FORMULAEXISTS(Scratch.A2),Scratch.A2,""),IF(FORMULAEXISTS(Scratch.A3),Scratch.A3,""),...,IF(FORMULAEXISTS(Scratch.An),Scratch.An,""))

That produces a loooong formula, but did the trick. Now, I'm trying to find a shapesheet way to identify the cell that contains the less number.

[wapperdude]

Since you have the min value, you can use that to find the cell...

Create another user cell with something like if(scatchA1=minval, "scratchA1", if(scratchA2=minval, "scratchA2", if....  ...)))))))))))))))))))))
The result of this long nested IF formula will be the name of the matching cell.

[Gustavo]

Cool tip,  thanks wapperd !

[vojo]

in general, for long formula's like this, you want to put pieces in several user cells, then combine.
Key reason is that its easier to debug.   This is true for this kind of test a value formula as well as geometry formulas