Excel Formula problem not returning reference of last row of a table

Question

i have a problema whit a formula to get the reference of the first and last row of a table.

i have create a table named "Income" that table start at row 10 and end at row 12

in a cell i use this formula to get the reference of the first row of the table:

=MIN(ROW(Income))

in a second cell i use this formula to get the reference of the last row of the table:

=MAX(ROW(Income))

The first formula return me the number 10 that is correct becouse the first row of the table start at row 10.

The second formula return me te number 10 too that is not correct becouse the last row of the table is at row 12.

Add the screenshot of the table

i don't understand why the two different formulas give me the same result?

Answer 1

Instead of using MAX() & MIN() function use AGGREGATE() function which wont require to hit CTRL+SHIFT+ENTER while exiting the edit mode as OP is using Excel 2019, and hence why the MAX() function was always returning 10 instead of 12 as it was returning an array using the ROW() function.

That said the formulas are:

• For MAX

=AGGREGATE(14,7,ROW(Income),1)

---

• For MIN

=AGGREGATE(15,7,ROW(Income),1)

---

Or, If ones to return the output of MAX & MIN row wise then

=AGGREGATE({14;15},7,ROW(Income),1)

---

The above would however needs to hit CTRL+SHIFT+ENTER while exiting the edit mode.

---

Note that the separators differs based on locale or regional settings. For OP it would be semi-colon --> ; instead of comma --> ,.

---