Find Max Difference

[Sean Timmons]

So, hoping I can explain this tersely.

Let's say today is the 15th of January.

I have records such as the below.

Col A Col B Col C
Rep Names Event Date of Event
Bob EventA 1/1/09
Sally EventA 1/5/09
Bob EventB 1/6/09
Bob EventA 1/7/09
Sally EventA 1/12/09

I have a separate table with each rep name in column A

The report will be only for the month of January.

In column B, I want to know, for each rep, the maximum number of days
between EventA.

So, for Bob, it would be 8. This because he had an EventA on 1/7, and today
is 1/15. So, I know we need to use the DAY() function within to determine
current day of month.

For Sally, the number would be 7 since eventA was 5th and 12th.

I know the formula would have to basically figure out that, 15("today"'s day
of month)-7 = 8, 7-1 = 6 kind of thing. Seems like an array formula.

Hoping this wasn't overly complex to read... Let me know if any questions,
and thank you in advance for any attempts!

 

[Jarek Kujawa]

this formula:
=MAX(IF(($A$1:$A$5=E1)*($B$1:$B$5="A"),$C$1:$C$5,""))-MIN(IF(($A$1:$A
$5=E1)*($B$1:$B$5="A"),$C$1:$C$5+0,""))
(CTRL+SHIFT+ENTER)
shows 7 for Sally and 6 for Bob

did not get yr explanation re TODAY function

 

[Shane Devenshire]

Hi,

Your title states finding the MAX difference, but your example finds the MIN
difference. Here is a formula for the MAX difference:

=MAX(($A$2:$A$6="Bob")*($D$1-$C$2:$C$6))

This is and array formula so press Shift+Ctrl+Enter to enter it.
D1 contains the date 1/15/2009.

 

[barry houdini]

Hi,

Your title states finding the MAX difference, but your example finds the MIN
difference.  Here is a formula for the MAX difference:

=MAX(($A$2:$A$6="Bob")*($D$1-$C$2:$C$6))

This is and array formula so press Shift+Ctrl+Enter to enter it.
D1 contains the date 1/15/2009.  

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire















- Show quoted text -

Presumably the max gap could also be between the start of the month
and the first occurence.

Try this formula

=MAX(FREQUENCY(ROW(INDIRECT("1:"&DAY(MIN(D1,DATE(YEAR(C2),MONTH
(C2)+1,0)))+1))-1,IF(A2:A6="Bob",IF(B2:B6="EventA",DAY(C2:C6)))))

Where D1 is "today's" date.

This is an "array formula" that needs to be confirmed with CTRL+SHIFT
+ENTER

I assume that today must be greater than the latest date shown. The
formula also works OK if "today's" date is later than the end of the
month in question. Replace "Bob" and "EventA" with required criteria

 

[Shane Devenshire]

Hi,

" Presumably the max gap could also be between the start of the month
and the first occurrence"

In which case the current date is irrelevant

Cheers,
Shane Devenshire

 

[barry houdini]

Hello Shane,

I'm not sure how you come to that conclusion from my comment.

If I'm reading it correctly the current date isn't irrelevant. I'm
assuming that Sean wants to get the maximum gap between instances of
"EventA" for Bob, given a start date of 1st of the month and end date
of "today". So the maximum gap could be between actual instances (as
with Sally) or could be between the last occurence and today (as with
Bob). I also assumed that the max gap could be between the 1st of the
month and the first occurence, if that was greater than any other gap,
but I've been wrong before........