Right, ignore my last re counting date entries that are the same, I have
managed to do it! Except fot the MIN bit.
Is there a way to adapt MIN so that it will ignore 0 values in a list?
--
tia

Jock

You can use an array* formula along the lines of:

=MIN(IF(A1:A1000,A1:A100,40000))

assuming your dates are in A1:A100.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must commit it with CTRL-SHIFT-ENTER instead
of the usual ENTER. If you do this correctly then Excel will wrap the
formula in curly braces { } when viewed in the formula bar - you must
not enter these yourself.

Dates are stored by Excel as the number of elapsed days since a
reference date of 1st Jan 1900, so current dates are about 39200 -
hence, the use of 40000 in the formula will treat this as a date
somewhere in the future, but you could use any number larger than
this.

Hope this helps.

Pete

On Jun 21, 10:52 am, Jock wrote:
Right, ignore my last re counting date entries that are the same, I have
managed to do it! Except fot the MIN bit.
Is there a way to adapt MIN so that it will ignore 0 values in a list?
--
tia

Jock

Try:-

=MIN(IF(A1:A1000,A1:A100))

entered as an array.

Mike

"Jock" wrote:

Right, ignore my last re counting date entries that are the same, I have
managed to do it! Except fot the MIN bit.
Is there a way to adapt MIN so that it will ignore 0 values in a list?
--
tia

Jock

That's got it.
Many thanks Mike

Jock


"Mike H" wrote:

Try:-

=MIN(IF(A1:A1000,A1:A100))

entered as an array.

Mike

"Jock" wrote:

Right, ignore my last re counting date entries that are the same, I have
managed to do it! Except fot the MIN bit.
Is there a way to adapt MIN so that it will ignore 0 values in a list?
--
tia

Jock

Thanks Pete,
I have tried both versions (with and without the ",40000" bit) and they both
give the same result.


Jock


"Pete_UK" wrote:

You can use an array* formula along the lines of:

=MIN(IF(A1:A1000,A1:A100,40000))

assuming your dates are in A1:A100.

* As this is an array formula, then once you have typed it in (or
subsequently edit it) you must commit it with CTRL-SHIFT-ENTER instead
of the usual ENTER. If you do this correctly then Excel will wrap the
formula in curly braces { } when viewed in the formula bar - you must
not enter these yourself.

Dates are stored by Excel as the number of elapsed days since a
reference date of 1st Jan 1900, so current dates are about 39200 -
hence, the use of 40000 in the formula will treat this as a date
somewhere in the future, but you could use any number larger than
this.

Hope this helps.

Pete

On Jun 21, 10:52 am, Jock wrote:
Right, ignore my last re counting date entries that are the same, I have
managed to do it! Except fot the MIN bit.
Is there a way to adapt MIN so that it will ignore 0 values in a list?
--
tia

Jock