Absolute/Relative addressing changes when entering formula

[mikerickson]

I select cell C2, type =MAX(D2:$M$1) in the formula bar, press Enter, and the cell contains the formula =MAX(D$1:$M2)

Try again, but a little different

I type =MAX($M$1:D2), press enter and the cell contains =MAX(D$1:$M2)

HUH ?!?

Excel 2019 for Mac, Mac OS Catilina 10.15.5

(At least typing =MAX(D2:INDEX($1:$1, 1, 13)) works as expected

Thank you for reading

 

[footoo]

I think Excel is producing the correct formula.

In the formula =MAX(D$1:$M2), the relative/absolute column and row references are the same as your input.

What do you think should be the start cell and end cell references from the range D2:$M$1 ?

 

[mikerickson]

I think that D2 and M1 should be the end points
And I do note that the ranges that result from dragging D$1:$M2 are the same ranges as if D2:$M$1 were dragged.
Just a bit confusing. I think that part of it has to do with D2 , M1 is the minor diagonal of the range, rather than the major diagonal D1 , M2

 

[footoo]

I think that D2 and M1 should be the end points
And I do note that the ranges that result from dragging D$1:$M2 are the same ranges as if D2:$M$1 were dragged.
Just a bit confusing. I think that part of it has to do with D2 , M1 is the minor diagonal of the range, rather than the major diagonal D1 , M2

For single area ranges the start point is the top left cell and the end point is the bottom right cell.