The challenge this week is as follows: given a range of arbitrary size in which each entry is either 0 or 1 and in which each row contains at least one occurrence of a 1, a single formula to return an array consisting of the relative column positions of the first occurrence of a 1 within each row.
For example, given the below in A1:E10:
the solution would be the array:
Readers may reference the range A1:E10 in their solution, though of course being aware that this choice is purely arbitrary and hence that any solution must also hold for a range of any size.
Readers should also note that the entries in the returned array are to be the relative column positions within the range (just as if we’d used MATCH on each of the rows within that range). As such, moving the above range to, for example, H1:L10 would have no impact on the output of any solution.
Also note that this is NOT a shortest formula challenge!
Solution next week. Good luck!