Re: How do I find address of cell containing maximum value

Tech-Archive recommends: Speed Up your PC by fixing your registry

On Mon, 29 Aug 2005 14:47:23 -0700, "Doug" <Doug@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

>I'd like to have the cell address returned along with a value when I use the
>MAX function. Is there a way to do that?

With the range in which the numbers are stored named "rng" (or you may
substitute the cell reference directly in the formula, the following **array**
formula will give you the cell address of the first cell to contain that
maximum value.

To enter an *array* formula, after typing or pasting in the formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula.

=ADDRESS(MAX((rng=MAX(rng))*ROW(rng)),
MAX((rng=$A$1)*COLUMN(rng)))

This will work for ranges comprising multiple rows/columns as well as for
ranges which are just a single row or column.

However, the range may not include more than 65,535 cells.

If that is a requirement, a VBA solution will probably be needed.


--ron
.

Quantcast