Calculating the interval difference between occurrences

Hi JB,

Since you have 0's in the range MAX may give -ve results If 0's NOT in the range., a little change to dewilk's formula.

=COUNTA(B$2:B$200)-LARGE(IF((B$2:B$200<>"")*(B$2:B$200=E2),ROW(B$2:B$200)),1)+1

with CTRL+SHIFT+ENTER. If a number doesn't found will return an error.

Hi dewilk & Haseeb.

Thanks for the prompt response.

I did try using the formula in cell F2. However, the result that I get is wrong. I am sorry if I was not clear enough. The formula should match the number in E2 & count from the bottom of the range in column B, to check when it had last occurred.

For e.g. 3 has just occurred very recently. So, the result in F5 should be '0'

I hope I am clearer this time.

Haseeb, I had come across another of your posts titled, "Difference in occurrence". Thought that this would be something similar.

Thanks for all the help.

-JB

P.S.: The numbers in column "F" are the results that I am looking for with the formula.
P.P.S.: Also, would you please explain to me what "CTRL+SHIFT+ENTER" is for...?

Originally Posted by Haseeb A

Hi JB,

Since you have 0's in the range MAX may give -ve results If 0's NOT in the range., a little change to dewilk's formula.

=COUNTA(B$2:B$200)-LARGE(IF((B$2:B$200<>"")*(B$2:B$200=E2),ROW(B$2:B$200)),1)+1

with CTRL+SHIFT+ENTER. If a number doesn't found will return an error.

See the attached.

These articles may give you some idea about Array Formulas.

Array Formulas, Array Formula Samples