Forum Discussion
Working out when a regular subscription changed from transaction lists
in H3: =MAXIFS(Table1[Amount], Table1[Name],G3)
in I3: =MINIFS(Table1[Date], Table1[Name],G3, Table1[Amount],H3)
if a person increased the subsciption level, but reduces the Number of subsruiptions. ie Was paying £40 (20X2) and changed to £22 (22x1)
Not sure I understand:
- Could you post a sample with the expected result?
- What version of Excel do you run + on which OS (Windows/Mac)?
I run windows and office 365
The amount of data lines are also big. Will have (up to 100,0000 lines of data) will that be an issue?
So below Bob had two subsciptions at £20 (paid £40) he increased to £22 on the 2/3/23 so that is wat we want to see.
However as he reduced his overall payents the formula looks for the increase.
I might be able to get the number of subsciption in, and hence devide £40 by 2.
But is there a formula that can find the last change (up or down)
up to 100,0000 lines of data => I would recommend you do it with Power Query
I assumed your Dates are sorted/ordered as shown in your picture, otherwise a change must be made to the query...
Could you test the attached query with some more data and let me know of any issue?
(Put your data in the table sheet Input > Switch to sheet Output > Right-click in the Green table > Refresh)