Cell info from =MAX(A1:A999)

genyue

New Member
Joined
May 10, 2013
Messages
6
I used the formula =MAX(A1:A999)

the returned value is 1.7933
cell A998 contains value 1.7933

What formula can I use to let me know which cell the value was taken from?
Of course, I can go thought the 999 values manually until A998, but there would take a long time.

I'm looking for something like that
=max-cell-info(A1:A999)
return result will be A998
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

I used the formula =MAX(A1:A999)

the returned value is 1.7933
cell A998 contains value 1.7933

What formula can I use to let me know which cell the value was taken from?
Of course, I can go thought the 999 values manually until A998, but there would take a long time.

I'm looking for something like that
=max-cell-info(A1:A999)
return result will be A998

Consider...
ItemMax
2020
15Count
163
20Address List
12$A$2
20$A$5
$A$7

<colgroup><col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;" width="91"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;" width="88"> <tbody>
</tbody>


B2, just enter:
Rich (BB code):
=MAX(A2:A7)
B4, just enter:
Rich (BB code):
=COUNTIF(A2:A7,B2)
B6, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($B$6:B6)<=$B$4,CELL("address",INDEX($A$2:$A$7,
  SMALL(IF($A$2:$A$7=$B$2,ROW($A$2:$A$7)-ROW($A$2)+1),
  ROWS($B$6:B6)))),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I used the formula =MAX(A1:A999)

the returned value is 1.7933
cell A998 contains value 1.7933

What formula can I use to let me know which cell the value was taken from?
When running your MAX formula, can you be certain there will not be more than one cell in the range containing that MAX value?
What do you want to happen if there is more than one cell containing the maximum value?

Edit: Ah, I see Aladin has offered at least one alternative for that scenario. :)
 

genyue

New Member
Joined
May 10, 2013
Messages
6
I got my answers.

Thank you Sachin and Aladin so very very much for the answers.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,971
Messages
5,599,109
Members
414,289
Latest member
sonintebil

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
Top