Row Number Of Actual Cell That Contains The Maximum Value In A Range

anwaee2

Board Regular
Joined
Sep 13, 2012
Messages
151
Office Version
  1. 2011
Platform
  1. MacOS
Is there a formula to obtain the row number not the position number of the maximum value in a range such as A11:A50? I can do it in VBA but was hoping I could do it with a formula. I have tried several things but continue to get errors. Row doesn't seem to work except with cell address only. I know this doesn't work: =ROW(CELL("address",INDEX(A11:A50,MATCH(MAX(A11:A50),A11:A50,0))))
Thanks in advance for any help I may get. Mr. Excel forum is the best.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Here you go.

Excel Formula:
=MAX((B:B<>"")*(ROW(B:B)))
 
Upvote 0
How about
Excel Formula:
=INDEX(ROW(A11:A50),MATCH(MAX(A11:A50),A11:A50,0))
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Here you go.

Excel Formula:
=MAX((B:B<>"")*(ROW(B:B)))
Sorry, that doesn't seem to work for me. All I get is a zero for the answer. Thanks for trying, maybe I am missing something.
 
Upvote 0
Sorry, I didn't change the columns to A.

Excel Formula:
=MAX((A11:A50<>"")*(ROW(A11:A50)))
 
Upvote 0
That will just return the last used row in the range, not the row of the mac value. ;)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top