How can I calcuyalte the minimum of a set of numbers or arrays while ignring zeros.
For example:
Assume A=1, B=2, C=0, D=4.
Min (Min(A, Min(B,C)),D);
Since I want to ignore zero values, the minimum should be 1. But as written in AB, the minimum returned is zero. What changes do I need to make to this code to ensure AB will ignore zero values?
Similarly, how could I calculate the average while ignoring zero values?
Thank you very much!
There are couple of possible solutions. You can simply remove zeros from input arrays, by keeping the last non-zero value
A = ValueWhen( A !=0, A ); // remove zero keep previous value
// do the same with B, C, D
Alternatively if all numbers are positive integers as in your example, you can just add Max( result, 1 ) on the result so it never drops to zero
One note though: You did not think about the case when all four are zero? What then?
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: =MIN(IF($A$1:$E$7 <>0,$A$1:$E$7))
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.
See this if it helps
AFL Function Reference - SPARSECOMPRESS (amibroker.com)
So Compress also non-zero values in the array, then get index of last non-zero value. Then get your other calculations like average etc.
It says this function will compress all non-zero values to the end.
Also, as Tomasz mentioned, what if all values/variables are zero?
@ITradeandIWin, this seems to work for me if you are using arrays.
Since you are replacing all zeros with values that are already present in the array, the search for the minimum value in the array will return the lowest(1) existing non-zero value.
So de facto zeros are ignored.
For scalars, like in your example, you can write a simple custom function:
function MinNZ(a, b) {
result = Min(a, b);
if (a == 0 AND b != 0)
result = b;
if (a != 0 AND b == 0)
result = a;
return result;
}
A=1;
B=2;
D=0;
E=4;
Min_value = MinNZ(MinNZ(A, MinNZ(B, D)), E);
Title = "Min Value : " + Min_Value;
But again.... what if all values/variables are zero?
1) To get the lowest value form an array as a scalar use: LastValue(Lowest(your_array));
SparseCompress won't work here, because after compression each array will have values that correspond to a unique set of timestamps. For example, the value at A[i] might be from 2020-01-05 while the value at B[i] is from 2020-01-06. It would be invalid to compare those two values.
I would do something like this:
AA = IIf(A != 0, A, 1e9);
BB = IIf(B != 0, B, 1e9);
CC = IIf(C != 0, C, 1e9);
DD = IIf(D != 0, D, 1e9);
minValue = Min(AA, Min(BB, Min(CC, DD)));
Note that it is possible for minValue to be assigned the value 1e9 if all the input arrays were equal to 0 for that bar.
Yes, incase we are comparing by index position, sparse wont work but i was looking it at it from Array min to Array min comparison.
Lowest of Array_A and B/C/D and so on.
So Lowest of each in scalar after sparse, it was just a thought since non-zero values are compressed and he was looking to find Average and other calculations.
After sparse, if there are only x values non-zero, then he can sum last X and divide by that count.
@nsm51 I agree the OP was not entirely clear on the problem to be solved. I provided a solution that corresponds to my interpretation of what he's trying to do.
Both mine and @mradtke solutions essentially do the same thing - replace zero with non-zero value. My solution uses previous non-zero value, @mradtke solution uses large fixed value (1e9).
Still OP did not say what he expects when all values are zero.
My solution obviously assumed that OP wants arrays because Array is mentioned in SUBJECT of the topic.
After reading @ITradeandIWin response it seems that he is more after @mradtke solution eventually with replacing 1e9 with Null if all inputs are zeros
AA = IIf(A != 0, A, 1e9);
BB = IIf(B != 0, B, 1e9);
CC = IIf(C != 0, C, 1e9);
DD = IIf(D != 0, D, 1e9);
minValue = Min(AA, Min(BB, Min(CC, DD)));
minValue = IIF( minValue == 1e9, Null, minValue ); // replace 1e9 with Null
This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.