*TYPOS*

Both are *array* formulas.

For *last* minimum entry:

=ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)), 9)

For *last* maximum entry:

=ADDRESS(MAX((I9:I373=MAX(I9:I373))*ROW(I9:I373)), 9)
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"RagDyeR" wrote in message
...
Both are *array* formulas.

For *last* minimum entry:

=ADDRESS(MAX((I9:I373=MIN(I9:I30))*ROW(I9:I373)),9 )

For *last* maximum entry:

=ADDRESS(MAX((I9:I373=MAX(I9:I30))*ROW(I9:I373)),9 )


--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"bookman3" wrote in message
...
I know the values, I just want to display the cell references of the latest
largest and smallest values, ignoring zeros as the zero values are next to
future dates
--
bookman


"T. Valko" wrote:

Ok, do you specifically need the cell address or do want the actual number
but the only way you know how to find it is by using the address formula?

We can do either one.

--
Biff
Microsoft Excel MVP


"bookman3" wrote in message
...
No the nmubers are random, actually the column to the left is in date
order
and this column has the amount relating to that date
--
bookman


"T. Valko" wrote:

Are your numbers sorted in descending order?

--
Biff
Microsoft Excel MVP


"bookman3" wrote in message
...
Hi

That works a treat.

I have 2 supplementary questions

If the same minimum value appears more than once in the list,how can
I
find
the last entry?

I replaced SMALL with MAX. That worked but if there are more than 2
maximum
values in the list, how can I find the last entry?
--
bookman


"bookman3" wrote:


Hi

{=ADDRESS(MIN(IF($I$9:$I$373=MIN($I$9:$I$373)*($I$ 9:$I$373<0),ROW($I$9:$I$373))),MIN(IF($I$9:$I$373 =MIN($I$9:$I$373)*($I$9:$I$373<0),COLUMN($I$9:$I$ 373))))}

This formula only works fine except when there are zeros in column I
even
though the formula is looking at vlaues < 0
For example: rows 9 to 84 have values, rows 85 to 373 have 0.
The formula gives the result of I85.
If rows 85 to 373 are left blank the result is correct.

Can anyone help?
--
bookman