Hi, I am counting the number of times (rows) a user made transactions before they made their highest yet.
These are the criteria:
1. If a person's transaction is same for all the dates, I am returning the first date and number of counts before the highest transaction will be 0.
2. If the person's highest transaction is the first one, I am returning the first date and number of counts as 0.
3. If the person's highest transaction is after few numbers of transactions , I am counting the number of transaction before that highest one. For example, here for userID 104, the highest is $200 and 5th column is showing on which date he made, 7th columns shows the max transaction. Here I am counting the number of rows before $200, which will be 5. (which I have showed in a worksheet 2)
I believe I am good upto this step. Right now, I am getting the counts for individual users. But I want to see how many users performed the transaction before they made their highest transaction. I want the "Counts before highest" as discrete so that I can count the number of users in each counts . For my sheet 2, I want to return something like this. 2nd Column tells 16 users made highest transaction on their 1st one, 4 users made 1 transaction before they made their highest and so no...
Right now, if I remove the user ID and date columns, it gives me only the sum total .