|
OfficeTips Home || VBA Section || General Section || Download Section || Privacy Policy |
Get the Address Of The Cell That Contains The Maximum Value |
|
We know that to find the
Maximum value in a given range we use the Max worksheet
function.
=MAX(<range>)
where <range> is the range of
cells containing the values. We have also seen how to sum up top N
values in a given range of cells in an earlier tip.
Now in some cases we might be more interested in obtaining the cell
address of the cell containing the maximum value rather than the value
itself. We can determine that by using the use the following
formula :
=ADDRESS(MATCH(MAX(<range>),<range>,0)+ROW(<range>)-1,COLUMN(<range>))
<range> represents the range of cells
to look up. Similarly you can also use the above formula with the MIN
function, which finds the cell address of the smallest value in
a range of cells.
|
Copyright 1999-2018 (c) Shyam Pillai. All rights reserved.