Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|