Min Year in an Array
 

I am looking at an array and trying to return the earliest year. I have
tried this:

{=IF(Export!$A$2:$A$282=A3,YEAR(MIN(Export!$L$2:$L $282,"")))}

Column A is checking the array for a specific name. If it finds it, I would
like the output to be the YEAR of the earliest date in L.

As output, I get either "#VALUE!" or "FALSE".

Thanks.

Min Year in an Array
 

Try this array formula** :

=MIN(IF(Export!$A$2:$A$282=A3,YEAR(Export!$L$2:$L$ 282)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If A3 is not found in Export!$A$2:$A$282 the formula will return 0.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am looking at an array and trying to return the earliest year. I have
tried this:

{=IF(Export!$A$2:$A$282=A3,YEAR(MIN(Export!$L$2:$L $282,"")))}

Column A is checking the array for a specific name. If it finds it, I
would
like the output to be the YEAR of the earliest date in L.

As output, I get either "#VALUE!" or "FALSE".

Thanks.

Min Year in an Array
 

Thanks. This works fine however sometimes there are a few blank values that
come up in column L and causes the output to be 1900. Is there away to make
them blanks? Specifically, ignore the blanks.

Have a good weekend.

"T. Valko" wrote:

Try this array formula** :

=MIN(IF(Export!$A$2:$A$282=A3,YEAR(Export!$L$2:$L$ 282)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

If A3 is not found in Export!$A$2:$A$282 the formula will return 0.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I am looking at an array and trying to return the earliest year. I have
tried this:

{=IF(Export!$A$2:$A$282=A3,YEAR(MIN(Export!$L$2:$L $282,"")))}

Column A is checking the array for a specific name. If it finds it, I
would
like the output to be the YEAR of the earliest date in L.

As output, I get either "#VALUE!" or "FALSE".

Thanks.

Min Year in an Array
 

"PAL" wrote:
.. sometimes there are a few blank values that come up in column L
and causes the output to be 1900. Is there away to make them blanks?
Specifically, ignore the blanks.

Try adding the check on "col L 0" like this, array-entered:
=MIN(IF((Export!$A$2:$A$283=A3)*(Export!$L$2:$L$28 30),YEAR(Export!$L$2:$L$283)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---