Number of days between entries

[GoKats78]

I have a data set in which I am looking to find the number of days between the two most recent entries based on the area in which the occurrence happened

Example ..the number of days between the last occurrence in Area A2 and the most recent previous one ..so in this case it would be 8/28/2014 minus 7/10/2014 = 49 days

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(LARGE((B2:B18="A2")*(A2:A18),{1;2})*{1;-1})

If you want you can substitute "A2" by a cell reference

M.

 

[GoKats78]

That did exactly what I needed it to do! the data set I provided wasn't exactly what I had ..but I was able to modify the provided formula to work!

That odd thing is if there is no match I was getting a number in the 40000's! with a little IF statement that went away.
Thanks so much for you help!

 

[GoKats78]

It works...but I really want to know exactly what each portion does...

 

[Marcelo Branco]

It works...but I really want to know exactly what each portion does...

Select the cell with the formula and use the tool

Formulas > Evaluate formula

You can see, step by step, what the formula does

M.

 

[GoKats78]

I saw that...but I don't know what the {1;2})*{1;-1}) does..or why the formula needs them!
I need to figure that out so when I need to do one of these I can figure it out!

 

[Marcelo Branco]

Take a look at Help file about the function LARGE
You see LARGE(array,k) where k is the position, i.e., 1 = Largest; 2 = 2nd_largest and so on

LARGE(.........,{1;2})
returns an array containing two values: the Largest value; and the 2nd_largest

*{1;-1) multiplies the Largest by 1 and the 2nd_largest by -1 resulting in
Largest - 2nd_largest

Hope I made myself clear

M.

 

[GoKats78]

I was just about to ask (I worked my way through the rest)..but was not sure what the 1,-1 did..I knew what it did...just not sure why!
One again..Thanks so much!

 

[Marcelo Branco]

I was just about to ask (I worked my way through the rest)..but was not sure what the 1,-1 did..I knew what it did...just not sure why!
One again..Thanks so much!

You are welcome.

M.
ps: i edited my post above.