Since the MIN() function in Sheets only returns a single value and there is no way to make it work with ARRAYFORMULA, I wanted to make a custom function that would take two arrays and compare the values at each entry, and return an array of the minimums. (I know there's a workaround that uses QUERY, but it wasn't going to work for my purposes)
What I have right now will take two arrays with one row and work perfectly. Unfortunately, it breaks when more than one row is introduced. I'm not sure why, so I'm lost on how to move forward. How can I make it work for any size arrays?
When I feed it any two dimensional range, it throws an error:
"TypeError: Cannot set property '0' of undefined"
on this line finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);
The current ""working"" code:
function MINARRAY(arr1, arr2) {
if (arr1.length == arr2.length && arr1[0].length == arr2[0].length)
{
var finalarray = [[]];
for (x = 0; x < arr1.length; x++)
{
for(y = 0; y < arr1[x].length; y++)
{
finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);
}
}
return finalarray;
}
else
{
throw new Error("These arrays are different sizes");
}
}
finalarray
is a 2D array. [[]]
sets only the first element of finalarray
to a array. It is needed to set all elements of finalarray
to a array. Inside the loop, add
finalarray[x]=[]//set `x`th element as a array or finalarray[x]= finalarray[x] || []
finalarray[x][y] = Math.min(arr1[x][y], arr2[x][y]);
Alternatively,
finalarray[x] = [Math.min(arr1[x][y], arr2[x][y])]