Using MAX or MIN

E

Earl Kiosterud

Red,

=MATCH(MAX(A2:A11),A2:A11,0)

This will give an index pointing to the value in the range where the max
value is. If there are two identical values, it returns the first.

This is used with INDEX to point into another array to retrieve a value, if
that's what you'll be doing.
=INDEX(E2:E11,MATCH(MAX(A2:A11),A2:A11,0))
 
O

Old Red One

Many thanks for your News Group reply to my MAX inquiry (How to find
cell....).

Your response included a suggested formula "to point to another array to
retrieve a value." I have never used INDEX, and I'm not sure I understand
'another array.' Could you illuminate, please.
 
E

Earl Kiosterud

Red,

Let A2:A11 be the Item Number column in an inventory table, and B2:B11 be
the Quantity column. The columns are different than the examples in my
earlier post. We want to find the item number of the item we have the most
of.

Let's say =MATCH(MAX(B2:B11),B2:B11,0) returned 6, meaning the sixth entry
in B2:B11 was the largest. Now you want to go into the Item Number (A2:A11)
column to retrieve the corresponding item number.

Embedding that in an INDEX function:

=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)) will return the corresponding
Item Number from column A. This is the Item number of the item we have the
most of. Note that there could be multiple items with this quantity, and
this would find only the first.

Note that you can, in edit mode, select an expression, like
MATCH(MAX(B2:B11),B2:B11,0), then press F9, and it will evaluate the
expression, replacing it with the result. So you'd see:
INDEX(A2:A11,6)
Be sure not to press Enter, or you'll have permanently have changed the
formula.. Press Esc to get out of edit mode.
 
O

Old Red One

Thanks again. I will work on this. It seems this will be very worthwhile
knowledge, once familiar with it. I'll have to become aware of 'edit
mode.' More later - probably. Red.
Earl Kiosterud said:
Red,

Let A2:A11 be the Item Number column in an inventory table, and B2:B11 be
the Quantity column. The columns are different than the examples in my
earlier post. We want to find the item number of the item we have the
most of.

Let's say =MATCH(MAX(B2:B11),B2:B11,0) returned 6, meaning the sixth entry
in B2:B11 was the largest. Now you want to go into the Item Number
(A2:A11) column to retrieve the corresponding item number.

Embedding that in an INDEX function:

=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)) will return the corresponding
Item Number from column A. This is the Item number of the item we have
the most of. Note that there could be multiple items with this quantity,
and this would find only the first.

Note that you can, in edit mode, select an expression, like
MATCH(MAX(B2:B11),B2:B11,0), then press F9, and it will evaluate the
expression, replacing it with the result. So you'd see:
INDEX(A2:A11,6)
Be sure not to press Enter, or you'll have permanently have changed the
formula.. Press Esc to get out of edit mode.
Click to expand...
 
E

Earl Kiosterud

Red,

Excel is normally in Ready mode (look at the far left of the status bar,
lower left of Excel window). Edit mode is entered when you double-click a
cell, or press F2. Also when you've clicked into the formula bar. Now
instead of "Ready," you see "Edit." If the cell contains a formula, you see
the formula in edit mode, and can edit it. When you're back in Ready mode
(you've pressed Enter or Esc), you see the results of the formula.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
Old Red One said:
Thanks again. I will work on this. It seems this will be very worthwhile
knowledge, once familiar with it. I'll have to become aware of 'edit
mode.' More later - probably. Red.
Click to expand...
 
Top