Prev: Pivot Table Defaults
Next: Normalize/concatenate
From: pete on 9 Mar 2010 14:08
Hi,

I have a range of dates and times, and the following will give me the latest
time:
{MAX((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}

However, I now want the earliest time, but "MIN" does not work:
{MIN((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}

What is the proper formula?
Thanks.
From: Fred Smith on 9 Mar 2010 14:29
Min is the right function.

What does "doesn't work" mean? Also, what's in B13?

Regards,
Fred

"pete" <pete(a)discussions.microsoft.com> wrote in message
news:902A7A53-1A8A-4B2A-9CC4-B2A88E7EBFEE(a)microsoft.com...
> Hi,
>
> I have a range of dates and times, and the following will give me the
> latest
> time:
> {MAX((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
>
> However, I now want the earliest time, but "MIN" does not work:
> {MIN((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
>
> What is the proper formula?
> Thanks.

From: Bob Phillips on 9 Mar 2010 14:31
Try

=MIN(IF(Data!$D$2:$D$5000=B13,Data!$F$2:$F$5000))

--

HTH

Bob

"pete" <pete(a)discussions.microsoft.com> wrote in message
news:902A7A53-1A8A-4B2A-9CC4-B2A88E7EBFEE(a)microsoft.com...
> Hi,
>
> I have a range of dates and times, and the following will give me the
> latest
> time:
> {MAX((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
>
> However, I now want the earliest time, but "MIN" does not work:
> {MIN((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
>
> What is the proper formula?
> Thanks.


From: pete on 9 Mar 2010 15:25
That works! Thanks a bunch!

"Bob Phillips" wrote:

> Try
>
> =MIN(IF(Data!$D$2:$D$5000=B13,Data!$F$2:$F$5000))
>
> --
>
> HTH
>
> Bob
>
> "pete" <pete(a)discussions.microsoft.com> wrote in message
> news:902A7A53-1A8A-4B2A-9CC4-B2A88E7EBFEE(a)microsoft.com...
> > Hi,
> >
> > I have a range of dates and times, and the following will give me the
> > latest
> > time:
> > {MAX((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
> >
> > However, I now want the earliest time, but "MIN" does not work:
> > {MIN((Data!$D$2:$D$5000=B13)*(Data!$F$2:$F$5000))}
> >
> > What is the proper formula?
> > Thanks.
>
>
> .
>
 | 
Pages: 1
Prev: Pivot Table Defaults
Next: Normalize/concatenate