MIN ARRAY FORMULA

[bookman3]

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?

 

[RagDyer]

I'm assuming that you want the cell address of the minimum value in a
column.

Since your dealing with a single, *known* column (Column I), I don't
understand why your calculating the second argument of the Address()
function.
Simply plug in "9" for the ninth column which is "I".

If my assumptions are correct, this should work for you:

=ADDRESS(MATCH(SMALL(I9:I373,COUNTIF(I9:I373,0)+1),I9:I373,0)+8,9)

 

[bookman3]

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?

 

[T. Valko]

Are your numbers sorted in descending order?

 

[bookman3]

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

 

[T. Valko]

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.

 

[bookman3]

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

 

[T. Valko]

Ok, I'm assuming that latest means the furthest down the column. Like this:

A1 = 90
A2 = 5
A3 = 90
A4 = 5

Max address would be A3. Min address would be A4

Address for the max:

=ADDRESS(LOOKUP(2,1/(I9:I373=MAX(I9:I373)),ROW(I9:I373)),9,4)

Address for the min:

Array entered**

=ADDRESS(MAX(IF(I9:I373=MIN(IF(I9:I373>0,I9:I373)),ROW(I9:I373))),9,4)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[RagDyeR]

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!
=====================================================

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

 

[RagDyeR]

*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!
=====================================================


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!
=====================================================

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

 

[RagDyeR]

*BAD* morning -

Correction for *last* minimum entry:

=ADDRESS(MAX((I9:I373=SMALL(I9:I373,COUNTIF(I9:I373,0)+1))*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.

--

Regards,

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


*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!
=====================================================


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!
=====================================================

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

 

[T. Valko]

For *last* minimum entry:
=ADDRESS(MAX((I9:I373=MIN(I9:I373))*ROW(I9:I373)),9)

That won't exclude 0s.

 

[RagDyer]

I'll assume by now, you've seen my last *VERY BAD MORNING* revision!

Tough to prioritize time when two transformers go at the same time.<bg>

 

[T. Valko]

Tough to prioritize time when two transformers go at the same time.<bg>

I can relate!