MINIF ????

J

Jock

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?
 
P

Pete_UK

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

=MIN(IF(A1:A100>0,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
 
J

Jock

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


Jock


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

=MIN(IF(A1:A100>0,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
Click to expand...
 
Top