Thank you Tomasz!
Your two possible solutions do not achieve my goal. The first solution assigns the previous value in the array to replace zeros. But I want zeros to be removed or ignored. The second solution, introduces a floor for the minimum at 1 or some other user assigned value. But here too, I do not want another number to serve as the floor.
In Excel, once can easily ignore zeros by combining the MIN and If functions. For example:
or by using the inbuilt MINIFs function.
Alternatively, one could simply have Excel replace all zeroes with a blank: " ".
There seems to be no comparable feature to Excel's MINIFS or MIN(IF(…. combo in AB.
I attempted to assign AB’s “Null” value to the array instead of zero, but then subsequent calculations failed. I tried to assign a blank text space (“ “) to replace zeroes in the hope the blank space would be ignored by AB, as they would be in Excel. That also failed.
The only other alternatives I can think of are
A. Assign a dummy value say: 999999 in place of zeroes. But then it means the array cannot be used elsewhere, as this dummy value could corrupt other calculations.
B. Hard code the various scenarios. For example
MIN_EX_ZERO = IIF( A >0 AND B > 0 AND C > 0 AND D >0, Min (Min(A, Min(B,C)),D); IIF (A > 0 AND B > 0 AND C > 0 AND D==0, Min(A, Min(B,C))
And so on…
But this would be very tedious and thus inadvertently introduce coding errors. For a simple set of four elements as in my example (A,B,C,D), I would have to hard code sixteen (2^4) possible scenarios --- (the “Power Set”), including the set of all zeroes. For five elements, it would be thirty two! scenarios 2^5). This is neither efficient, nor sustainable, and very prone to error.
Any ideas, please! Thank you very much.