Author Topic: Finding the smallest number and >0 in a set of cells  (Read 770 times)

0 Members and 1 Guest are viewing this topic.

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Finding the smallest number and >0 in a set of cells
« on: February 25, 2022, 10:09:09 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4374
  • Ideas Visio-lized into solutions
Re: Finding the smallest number and >0 in a set of cells
« Reply #1 on: February 26, 2022, 10:49:59 AM »
Shape sheet only solution or code solution OK?
Visio 2019 Pro

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Finding the smallest number and >0 in a set of cells
« Reply #2 on: February 26, 2022, 01:22:28 PM »
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

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4374
  • Ideas Visio-lized into solutions
Re: Finding the smallest number and >0 in a set of cells
« Reply #3 on: February 26, 2022, 01:39:55 PM »
One more question, you are only interested in the smallest value and don't care what cell contains it, yes?
Visio 2019 Pro

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Finding the smallest number and >0 in a set of cells
« Reply #4 on: February 26, 2022, 01:46:46 PM »
for now, only the value, its my minimal functionality. I'll deal with the cell later.

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Finding the smallest number and >0 in a set of cells
« Reply #5 on: February 26, 2022, 03:17:29 PM »
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.
« Last Edit: February 26, 2022, 03:43:30 PM by Gustavo »

wapperdude

  • Global Moderator
  • Hero Member
  • *****
  • Posts: 4374
  • Ideas Visio-lized into solutions
Re: Finding the smallest number and >0 in a set of cells
« Reply #6 on: February 26, 2022, 06:47:23 PM »
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.
Visio 2019 Pro

Gustavo

  • Jr. Member
  • **
  • Posts: 59
  • The ultimate inspiration is the deadline.
Re: Finding the smallest number and >0 in a set of cells
« Reply #7 on: February 27, 2022, 08:48:56 PM »
Cool tip,  :D thanks wapperd !

vojo

  • Hero Member
  • *****
  • Posts: 1670
Re: Finding the smallest number and >0 in a set of cells
« Reply #8 on: March 01, 2022, 03:22:41 PM »
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