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]

please refer to my post #6, the formula is:
H4=IFNA(MATCH(B4,B5:B$13,),"n/a")
copy and paste above formula in H4.

That is exactly what is in my H4. The rest of that column has a mix of that formula and one with a zero after the comma. Neither of them work.

But why have you not addressed the other errors? And why have you not responded to any of my other questions, such as whether your data is in a table and, if so, why you are not using table syntax.

 

[shaowu459]

That is exactly what is in my H4. The rest of that column has a mix of that formula and one with a zero after the comma. Neither of them work.

But why have you not addressed the other errors? And why have you not responded to any of my other questions, such as whether your data is in a table and, if so, why you are not using table syntax.

i copied your data and paste in my workbook, so the data is not a table
which you can use structured reference. if you can accept answers in other language, i could explain more. meanwhile, i am using cell phone now, i can get access to my computer in about one hour.

 

[JenniferMurphy]

i copied your data and paste in my workbook, so the data is not a table
which you can use structured reference. if you can accept answers in other language, i could explain more. meanwhile, i am using cell phone now, i can get access to my computer in about one hour.

My data is in a table, so I am only interested in a solution that will work in a table.

If this is because of a language problem, then I apologize. I don't speak another language well enough to do anything useful. And if you have done this from your cell phone, then I am impressed. But the solutions proposed do not work.

I have a solution that works. I was just asking if there might be a better way.

 

[shaowu459]

My data is in a table, so I am only interested in a solution that will work in a table.

If this is because of a language problem, then I apologize. I don't speak another language well enough to do anything useful. And if you have done this from your cell phone, then I am impressed. But the solutions proposed do not work.

I have a solution that works. I was just asking if there might be a better way.

i made the formula yesterday on my computer, now i am preparing go to my office and using phone now. i will explain later, using table reference.

 

[shaowu459]

Please see my formula in E4, because you add more rows, so the range of the second parameter should be change to the cell outof your table range, hilighted in red. This is used to create an error for the last Event when matching. if use B5:B$15, the last row will return 1 becuase the last match will be match(B15,B15:B16,0).

I entered my formula in table E4, and select apply the formula for the whole column.

工作簿1.xlsx
	A	B	C	D	E	F	G
1
2
3		Event	Date	(Good) Match Solution	(Fails) Absolute Range w/Match	Comments
4		D	2024-6-11	8	8	New row 1	TRUE
5		A	2024-6-10	3	3		TRUE
6		B	2024-6-8	3	3		TRUE
7		C	2024-6-5	4	4		TRUE
8		A	2024-6-4	2	2	New row in niddle	TRUE
9		B	2024-6-2	5	5		TRUE
10		A	2024-5-30	5	5		TRUE
11		C	2024-5-25	n/a	n/a		TRUE
12		D	2024-5-20	1	1		TRUE
13		D	2024-5-19	n/a	n/a		TRUE
14		B	2024-5-10	n/a	n/a		TRUE
15		A	2024-5-5	n/a	n/a	New last row	TRUE
16
17		A				Event text added outside table
18
19		B				Event text added outside table
20
21		C				Event text added outside table
22
23		D				Event text added outside table
24
Sheet1

Cell Formulas
Range	Formula
D4:D15	D4	=IFERROR(MATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")
E4:E15	E4	=IFNA(MATCH([@Event],B5:B$16,),"n/a")
G4:G15	G4	=E4=D4

 

[shaowu459]

some errors occures in your post #7 because the last cell reference in the second parameter of Match unchanged for the expanded table range, still kept as B$13.

 

[JenniferMurphy]

Ok, but is this better than my solution? It works in all scenarios.

=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")

 

[shaowu459]

Ok, but is this better than my solution? It works in all scenarios.

=IFERROR(XMATCH([@Event],DROP([Event],ROW()-ROW(Table4[[#Headers],[Event]])),0),"n/a")

I think it depends on each person's choice. If I use it in my work, I will use my formula because there is only one match, which is clearer and easier to maintain. most importantly, i will always get right answers because i know how and why the formula runs and return values. For a same problem, there are often many ways to solve it, and I think it is better for users to be able to grasp, apply, and maintain it.

The above content is automatically translated by the machine.

 

[JenniferMurphy]

I think it depends on each person's choice. If I use it in my work, I will use my formula because there is only one match, which is clearer and easier to maintain. most importantly, i will always get right answers because i know how and why the formula runs and return values. For a same problem, there are often many ways to solve it, and I think it is better for users to be able to grasp, apply, and maintain it.

The above content is automatically translated by the machine.

If it works for you, great. It does not work for me.

I don't know what you mean by "only 1 match". Mine gets only 1 match, too. And since it uses table syntax, it grows and shrinks with the table. It requires zero maintenance.

I still have a couple of questions:

1. Is there a better way than what I came up with?
2. Is there any reason to choose XMatch over Match?
3. Is there any reason to choose IFNA over IFERROR?

 

[shaowu459]

If it works for you, great. It does not work for me.

I don't know what you mean by "only 1 match". Mine gets only 1 match, too. And since it uses table syntax, it grows and shrinks with the table. It requires zero maintenance.

I still have a couple of questions:

1. Is there a better way than what I came up with?
2. Is there any reason to choose XMatch over Match?
3. Is there any reason to choose IFNA over IFERROR?

"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.

my answers to your 3 questions:
1. i'm not sure how to answer ,because the standard about "better" really depends on everyone's like.
2.if you just make normal precise match, I think it is no difference between XMatch and Match. XMatch is a more powerfu match function, for example, if you want to match from the last to the first, XMatch is a good choice.
3.if the errors just come from there are no matching items for Match or Xmatch, so the errors must be #N/A, so IFNA could handle this error well. IFERROR could handle more errors and it certainly can deal with #N/A, in this case IFNA and IFERROR i don't see any difference. Since Microsoft has already invented the IFNA function, I think we can just use it to handle #N/A errors. Anyway, it seems a bit shorter.