MIN and Year Functions
 

I have multi row, multi column table. I am looking for the min value with
the formula below and want the output to be the earliest year..

{=MIN(IF((Worksheet!$A$2:$A$1967=A13)*(Worksheet!$ E$2:$E$19670),YEAR(Worksheet!$E$2:$E$1967),""))}

It works for a few rows, but for the most part I get "#value!"

Not sure why.

MIN and Year Functions
 

The error is probably being generated he

YEAR(Worksheet!$E$2:$E$1967)

If your dates (or some of them) aren't true Excel dates the YEAR function
will generate a #VALUE! error where the logical test of the IF function
evaluates to TRUE.

Check your dates and make sure they are true Excel dates.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have multi row, multi column table. I am looking for the min value with
the formula below and want the output to be the earliest year..

{=MIN(IF((Worksheet!$A$2:$A$1967=A13)*(Worksheet!$ E$2:$E$19670),YEAR(Worksheet!$E$2:$E$1967),""))}

It works for a few rows, but for the most part I get "#value!"

Not sure why.

MIN and Year Functions
 

Column E is reference this formula

=IF('Site Raw Data'!H2="","",'Site Raw Data'!H2). Perhaps, the empty ("") is
causing the problem.

"T. Valko" wrote:

The error is probably being generated he

YEAR(Worksheet!$E$2:$E$1967)

If your dates (or some of them) aren't true Excel dates the YEAR function
will generate a #VALUE! error where the logical test of the IF function
evaluates to TRUE.

Check your dates and make sure they are true Excel dates.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have multi row, multi column table. I am looking for the min value with
the formula below and want the output to be the earliest year..

{=MIN(IF((Worksheet!$A$2:$A$1967=A13)*(Worksheet!$ E$2:$E$19670),YEAR(Worksheet!$E$2:$E$1967),""))}

It works for a few rows, but for the most part I get "#value!"

Not sure why.

MIN and Year Functions
 

Perhaps, the empty ("") is causing the problem.

Yes, it probably is.

Change this array:

(Worksheet!$E$2:$E$19670)

To:

(Worksheet!$E$2:$E$1967<"")

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Column E is reference this formula

=IF('Site Raw Data'!H2="","",'Site Raw Data'!H2). Perhaps, the empty ("")
is
causing the problem.

"T. Valko" wrote:

The error is probably being generated he

YEAR(Worksheet!$E$2:$E$1967)

If your dates (or some of them) aren't true Excel dates the YEAR function
will generate a #VALUE! error where the logical test of the IF function
evaluates to TRUE.

Check your dates and make sure they are true Excel dates.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have multi row, multi column table. I am looking for the min value
with
the formula below and want the output to be the earliest year..

{=MIN(IF((Worksheet!$A$2:$A$1967=A13)*(Worksheet!$ E$2:$E$19670),YEAR(Worksheet!$E$2:$E$1967),""))}

It works for a few rows, but for the most part I get "#value!"

Not sure why.