Using an array formula I want to find the max value of each row of a range and get the resulting range to work with it further.
The problem occurs as soon as I add the MAX() statement since it does seem to behave strangely within an array formula. Even if you ad commands which will give you multiple values within the MAX() statement it does always only return one single value.
E.g. this will give you the ranges which I want to get the max of:
=ARRAYFORMULA(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))
The result looks like the following:
F1:I1
F2:I2
F3:I3
F4:I4
F5:I5
F6:I6
F7:I7
F8:I8
F9:I9
F10:I10
F11:I11
If I now add INDIRECT() to make those to actual ranges and add MAX() it should return the max of each of those ranges since the array formula should go through the ROW(E1:11) as it did bevor. However, the result of this new formula
=ARRAYFORMULA(MAX(INDIRECT(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))))
rather is one single value, the maximum of the first range.
I have even tried to bypass the problem by adding an IF() statement for the array formula to iterate through the rows. Doing so, it did give me a result for all 11 rows, however, the result always was the same (the max of the first row).
The new formula:
=ARRAYFORMULA(IF(ROW(E1:E11) = ROW(E1:E11); MAX(INDIRECT(ADDRESS(ROW(E1:E11); COLUMN() + 1; 4) & ":" & ADDRESS(ROW(E1:E11); COLUMN() + 4; 4))); ""))
The new result (left column are the results of the formula, trying to get the max of each row to its right):
10 7 10 4 1
10 10 8 1 2
10 4 5 9 4
10 10 10 2 2
10 10 10 5 10
10 10 6 9 5
10 4 5 7 3
10 6 9 4 7
10 5 5 7 3
10 9 2 3 10
10 10 3 9 10
