Formula Help Please
 

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,

--
Jerry

Formula Help Please
 

On Mon, 25 Jun 2007 04:26:00 -0700, Jerry Rogers
wrote:

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,


=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the formula,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.
--ron

Formula Help Please
 

=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the
formula,
hold down <ctrl<shift while hitting <enter. Excel will place braces
{...}
around the formula.

If I am not mistaken, the following "non-array entered" formula will also
work

=SUMPRODUCT(MAX((A1:A1000)*(B1:B1000="IP")))

Rick

Formula Help Please
 

Ron,
This is interesting. How does the second part of the formula work?
MAM

"Ron Rosenfeld" wrote:

On Mon, 25 Jun 2007 04:26:00 -0700, Jerry Rogers
wrote:

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,


=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the formula,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.
--ron

Formula Help Please
 

On Mon, 25 Jun 2007 07:03:01 -0700, mmccoog
wrote:

Ron,
This is interesting. How does the second part of the formula work?
MAM

The second part returns TRUE or FALSE which, in a multiplication, Excel will
interpret as 1 or 0.



"Ron Rosenfeld" wrote:

On Mon, 25 Jun 2007 04:26:00 -0700, Jerry Rogers
wrote:

Hi,
Here is a clip from the data I need to select from.

11-Jun IP D
12-Jun IP D
12-Jun IP D
13-Jun IP D
14-Jun IP D
14-Jun IP D
16-Jun IP D
17-Jun IP D
18-Jun IP D
18-Jun IP NG
18-Jun SP D

I need to write a formula that returns only the the most recent (highest)
date from column A, if the corresponding entry in column B equals "IP". In
other words, I want to know the date of the most recent entry of "IP" in
column B.

Does anyone have a solution for my problem?

Thanks,


=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the formula,
hold down <ctrl<shift while hitting <enter. Excel will place braces {...}
around the formula.
--ron


--ron

Formula Help Please
 

On Mon, 25 Jun 2007 09:54:05 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

=MAX((A1:A11)*(B1:B11="IP"))

This must be entered as an **ARRAY** formula. After typing in the
formula,
hold down <ctrl<shift while hitting <enter. Excel will place braces
{...}
around the formula.

If I am not mistaken, the following "non-array entered" formula will also
work

=SUMPRODUCT(MAX((A1:A1000)*(B1:B1000="IP")))

Rick

Yes, it should.
--ron