Question description:
This user has given permission to use the problem statement for this
blog.
I'm trying to write a formula that would find the percentage of Invoices that have stayed in the same Row rank for 2 months. The sheet I'm working on is labeled Top X and the formula should be written starting in Q200. The Row rank is found in column J, the Account ID is column B, and the Date range is in C.
Solved by G. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/08/2018 - 02:18
Hi welcome!
User
14/08/2018 - 02:18
Hello! Here's the workbook
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 02:20
Ok, I have the sheet open
Excelchat Expert
14/08/2018 - 02:21
Let me review the request
User
14/08/2018 - 02:21
No problem, let me know if you have any questions
Excelchat Expert
14/08/2018 - 02:23
column J -row rank, where is the month indicator?
User
14/08/2018 - 02:24
Q2:AZ2
User
14/08/2018 - 02:24
And Row C has Year and Month
Excelchat Expert
14/08/2018 - 02:25
But how do they relate to col J?
Excelchat Expert
14/08/2018 - 02:26
J200 = 273; is that ranked 1-10?
User
14/08/2018 - 02:27
The data was pulled from 18 months worth of invoices. Each Row is a rank based on the Invoice amount. We're trying to calculate what percentage of the invoices were constantly in the same Row
User
14/08/2018 - 02:28
So if a Row has 1, then it is the highest dollar amount contract that month
User
14/08/2018 - 02:29
Correspondingly, if the Row column = 100, it's the 100th highest dollar invoice that month
Excelchat Expert
14/08/2018 - 02:30
so cell J200 = 273, that means 273rd ranked amount, but for what month?
User
14/08/2018 - 02:30
That's correct and it's 201805 = March 2018
User
14/08/2018 - 02:30
Sorry, May
User
14/08/2018 - 02:31
yyyymm
Excelchat Expert
14/08/2018 - 02:32
column c has the yyyymm
Excelchat Expert
14/08/2018 - 02:32
ok
User
14/08/2018 - 02:32
Yup, C is the date
Excelchat Expert
14/08/2018 - 02:33
Ok, got the problem statement
Excelchat Expert
14/08/2018 - 02:33
Now need to find a formula or two
Excelchat Expert
14/08/2018 - 02:35
and need to search the entire range- 238K rows?
User
14/08/2018 - 02:36
Yup, Q 185, the yellow section right above, is the correct format
User
14/08/2018 - 02:37
It also has most of the logic... I just couldn't figure out how to search more than 1 month
Excelchat Expert
14/08/2018 - 02:37
Q185 is blank
User
14/08/2018 - 02:38
=COUNTIFS($J$4:$J$238732,"<=10",$C$4:$C$238732,Q$2)/COUNT($C$5:$C$238732)
User
14/08/2018 - 02:39
I must of sent you an old file. that's the solution above, though
Excelchat Expert
14/08/2018 - 02:39
ok, makes the denominator clear
User
14/08/2018 - 02:39
No worries, it's a really weird problem
Excelchat Expert
14/08/2018 - 02:41
So if J200=273 , was also 273 in DEC 15, it is counted?
User
14/08/2018 - 02:43
it's still in the 201-500 range
Excelchat Expert
14/08/2018 - 02:44
Ok, so really looking at P for the range bracket
User
14/08/2018 - 02:44
Yes
Excelchat Expert
14/08/2018 - 02:45
And dates Q2 example are not standard dates but text
User
14/08/2018 - 02:45
Yah, you can use Row 2 to match
User
14/08/2018 - 02:46
It has the corresponding text date
Excelchat Expert
14/08/2018 - 02:50
are we allowed to add helper columns?
Excelchat Expert
14/08/2018 - 02:50
I think this is too much for a single formula
User
14/08/2018 - 02:50
Yah, I'm the manager lol
Excelchat Expert
14/08/2018 - 02:50
at least to get started
Excelchat Expert
14/08/2018 - 02:54
We may run out of time, so this is how I think we can do this
Excelchat Expert
14/08/2018 - 02:55
create a helper column with ID&Rank Bracket& month
User
14/08/2018 - 02:56
I actually have the unlimited plan if you don't mind giving it a shot for the next 20 min. I've been working on this all day and . can't seem to make anything work lol
Excelchat Expert
14/08/2018 - 02:56
search where current rank = previous month rank
Excelchat Expert
14/08/2018 - 02:56
Ok, I will keep at it
Excelchat Expert
14/08/2018 - 02:57
If we run out of time , I will continue and send via site support
Excelchat Expert
14/08/2018 - 03:02
Sorry, lost the flow here
Excelchat Expert
14/08/2018 - 03:02
How do I know J200 =273 was in the same bracket last month?
User
14/08/2018 - 03:03
i would think a variation of a countif statement
Excelchat Expert
14/08/2018 - 03:04
These brackets are not consistent
User
14/08/2018 - 03:04
if Row is between blank and blank this month and the next month, count
Excelchat Expert
14/08/2018 - 03:04
what is blank and blank- different for 273
Excelchat Expert
14/08/2018 - 03:05
vs 1500
Excelchat Expert
14/08/2018 - 03:05
we can add a table that looks up bracket?
User
14/08/2018 - 03:06
Yah, that would be fine
Excelchat Expert
14/08/2018 - 03:12
Ok, table built
Excelchat Expert
14/08/2018 - 03:16
Ok, I think I have enough info to start
Excelchat Expert
14/08/2018 - 03:16
I will need to finish , and send via site support
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
