Is this the best way to calculate the number of events since the last one?

[JenniferMurphy]

The minisheet below is my attempt to calculate the number of events (not the number of days) that have occurred since the last time that event occurred.

Col B has the events. Col C has the dates when they have occurred. Cols D & E are parts of the solution. D is the row # of this event. E is the row # of the previous occurrence of that event. Col F puts these together and adds IfError. Is there a better way?

Note: This depends on the table being sorted by date (newest to oldest).

Take Function.xlsx
	B	C	D	E	F	G
3	Event	Date	Row #	Row #(Next)	# Events	Comments
4	A	6/10/24	1	4	4	Event A last occurred 4 events ago.
5	B	6/08/24	2	2	2	Event B last occurred 2 events ago.
6	C	6/05/24	3	3	3	Event C last occurred 3 events ago.
7	B	6/02/24	4	5	5	Event B last occurred 5 events ago.
8	A	5/30/24	5	#N/A	n/a	This is the first time event A occurred.
9	C	5/25/24	6	#N/A	n/a	This is the first time event C occurred.
10	D	5/20/24	7	1	1	Event D last occurred 1 event ago.
11	D	5/19/24	8	#N/A	n/a	This is the first time event D occurred.
12	B	5/10/24	9	#N/A	n/a	This is the first time event B occurred.
Count Between

Cell Formulas
Range	Formula
D4:D12	D4	=ROW()-ROW(Table4[[#Headers],[Event]])
E4:E12	E4	=MATCH([@Event],DROP([Event],[@[Row '#]]),0)
F4:F12	F4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")

 

[JenniferMurphy]

"only 1 Match" I mean there is only one MATCH function in my formula, a single MATCH solves the problem, do not need more functions and more complicate logic, maybe could be more easier to understand and maintain.

Aha! You mean there is only 1 function (besides IFNA), whereas I have XMatch, Row (twice), and Drop (plus IFERROR). Yes, that is simpler, but, alas, it doesn't work in the situations where I need it to work. Simple is good, but working is better.

 

[shaowu459]

Returning to the original question of your post, you asked whether there is a better solution, so I provided what I think is a better solution. i am not saying my solution is better, just offered for your reference.

 

[shaowu459]

Aha! You mean there is only 1 function (besides IFNA), whereas I have XMatch, Row (twice), and Drop (plus IFERROR). Yes, that is simpler, but, alas, it doesn't work in the situations where I need it to work. Simple is good, but working is better.

yes, i agree with you. Formulas are not necessarily better the shorter they are; universality and sufficient robustness are very important.

 

[Alex Blakenburg]

Since your current formula works and is quite compact, I am struggling with what would be considered better.
The only thing I can think of is to make independant of the list being sorted but that might only confuse the user.

This is definitely not compact but does make in dependent of the sort.

=LET(currEvent,[@Event],
currDate,[@Date],
sortList,SORTBY(Table1[[Event]:[Date]],[Date],-1,[Event],1),
currRow,MATCH(1,(CHOOSECOLS(sortList,1)=currEvent)*(CHOOSECOLS(sortList,2)=currDate),0),
nextRow,MATCH(1,(CHOOSECOLS(sortList,1)=currEvent)*(CHOOSECOLS(sortList,2)<currDate),0),
IFNA(nextRow-currRow,"n/a"))

 

[JenniferMurphy]

Since your current formula works and is quite compact, I am struggling with what would be considered better.
The only thing I can think of is to make independant of the list being sorted but that might only confuse the user.

This is definitely not compact but does make in dependent of the sort.

=LET(currEvent,[@Event],
currDate,[@Date],
sortList,SORTBY(Table1[[Event]:[Date]],[Date],-1,[Event],1),
currRow,MATCH(1,(CHOOSECOLS(sortList,1)=currEvent)*(CHOOSECOLS(sortList,2)=currDate),0),
nextRow,MATCH(1,(CHOOSECOLS(sortList,1)=currEvent)*(CHOOSECOLS(sortList,2)<currDate),0),
IFNA(nextRow-currRow,"n/a"))

Wow! I would have to study that one. But since my list will always be sorted, I really don't need anything more general. And the "user" is just me, but then I am easily confused.

Thanks

 

[Alex Blakenburg]

I agree, stick with your version. (not about you being easily confused though )