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 Customer Invoices that have stayed in the same Row rank (Column J) for 2 consistent months. The rankings are segmented based on the criteria in Column P.
The sheet I'm working on is labeled Top X and the formula should be inputted in Q200:AZ212. The Row rank is found in column J, the Account ID is column B, and the Date range is in C (note the date range is Text, but the corresponding Dates needed for segmenting the data are located in Q2:AZ2. Please let me know if you have any questions!
Solved by K. Y. in 46 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/08/2018 - 02:20
Hi
Excelchat Expert
14/08/2018 - 02:20
Welcome to Got it Pro
Excelchat Expert
14/08/2018 - 02:21
Please share the data and tell more about the requirement so that i can help better
User
14/08/2018 - 02:21
Hello, here is the workbook
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 02:22
Please tell a bit more about the requirement
User
14/08/2018 - 02:23
Starting in Q200, I'm trying to get the percentage of invoices that have stayed in the same range for 2 months in a row.
Excelchat Expert
14/08/2018 - 02:24
which sheet?
User
14/08/2018 - 02:24
Top X
User
14/08/2018 - 02:24
Starting on Q200
Excelchat Expert
14/08/2018 - 02:24
What do you mean by stayed in 2 months?
User
14/08/2018 - 02:26
Row J has a list of Rankings called Row. We have given a numerical rank to the highest dollar invoices starting with 1. We are looking to get the percentage of which customer's consistantly had the top Row ranks for 2 months. The desired Rank ranges are indicated in Column P
User
14/08/2018 - 02:28
To break it down more, we need the percentage of Account IDs (B:B) that have the same Row (J:J) range for two consistant months (Date Range = C)
User
14/08/2018 - 02:29
Row 2 of the Sheet has the same date format for reference and the section above Q200, Q185 is a good reference
Excelchat Expert
14/08/2018 - 02:30
Yes i understand, i think i solved Q185 for you
Excelchat Expert
14/08/2018 - 02:30
I gave one session before to you as well and you gave me 5 star for that
Excelchat Expert
14/08/2018 - 02:30
Let me figure this one out as well
User
14/08/2018 - 02:30
Awesome! So glad I got you again!
Excelchat Expert
14/08/2018 - 02:30
Just give me sometime
User
14/08/2018 - 02:30
Of course, let me know if you have questions
Excelchat Expert
14/08/2018 - 02:32
Sure
Excelchat Expert
14/08/2018 - 02:35
You require them to be same in two consequitive months?
User
14/08/2018 - 02:36
That's correct. So column Q doesn't need a result
Excelchat Expert
14/08/2018 - 02:36
Month starts from R
User
14/08/2018 - 02:37
Correct
Excelchat Expert
14/08/2018 - 02:37
You mean R does not need formula
User
14/08/2018 - 02:38
No, Q doesn't need a formula. R should get the percentage for Jan and Feb
Excelchat Expert
14/08/2018 - 02:38
Q has ranges filled in them, it would not have the formula anyway
User
14/08/2018 - 02:39
true
Excelchat Expert
14/08/2018 - 02:40
I am still trying to figure out what % Customers Same (2 mo) mean
User
14/08/2018 - 02:42
So if an Account ID (B) has invoices two months in a row (C) and the Row (J) rank of both invoices fall in the designated range (P), then count and get the percentage of all of those counts
Excelchat Expert
14/08/2018 - 02:44
I think i have figured out a way, let me share a file with you in sometime
User
14/08/2018 - 02:44
Awesome, thank you!
Excelchat Expert
14/08/2018 - 02:50
Are you there?
User
14/08/2018 - 02:51
Yes
User
14/08/2018 - 02:55
any chance you could share via a Google Doc link?
User
14/08/2018 - 02:57
No problem. Would you be able to separate the page and send by itself?
User
14/08/2018 - 02:57
I'm going to keep the time going until we can get the file shared
Excelchat Expert
14/08/2018 - 02:58
This is the file
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 02:58
Did you get the file
Excelchat Expert
14/08/2018 - 02:58
Please have a look
Excelchat Expert
14/08/2018 - 02:59
I have written the steps about what i did on sheet 1
Excelchat Expert
14/08/2018 - 02:59
on right
Excelchat Expert
14/08/2018 - 02:59
Do you see it?
Excelchat Expert
14/08/2018 - 02:59
I have added another column for range in which they belong
and have sorted the data by ID, then month and then by range
then have added another column using if statement and have tageed 1 or 0..1 if same as prev month otherwise 0
then have counted that in formula
Not able to reply there as the file is being uploaded i guess
Excelchat Expert
14/08/2018 - 02:59
Please let me know if this helps
Excelchat Expert
14/08/2018 - 02:59
Looking forward to good rating and reviews from You
Excelchat Expert
14/08/2018 - 02:59
It was a tricky question, i must say
User
14/08/2018 - 03:00
Of course, I'll check all the prompted reviews and write you a nice custom one as well. I really appreciate the help
Excelchat Expert
14/08/2018 - 03:01
Thanks a lot
Excelchat Expert
14/08/2018 - 03:01
I am sure this will help you/guide you to go forward
Excelchat Expert
14/08/2018 - 03:02
Did you see the file?
User
14/08/2018 - 03:03
I did not
Excelchat Expert
14/08/2018 - 03:03
Did you download?
User
14/08/2018 - 03:04
it never popped up
Excelchat Expert
14/08/2018 - 03:05
Oh
Excelchat Expert
14/08/2018 - 03:05
It is showing from my side to be delivered
Excelchat Expert
14/08/2018 - 03:05
See this
[Uploaded an Excel file]
Excelchat Expert
14/08/2018 - 03:05
I removed the data
Excelchat Expert
14/08/2018 - 03:05
made it a very small file
Excelchat Expert
14/08/2018 - 03:05
You can just implement it the same way in your excel
User
14/08/2018 - 03:05
Thanks! Loading now
Excelchat Expert
14/08/2018 - 03:05
Just have a look at what i did and let me know if that helps
User
14/08/2018 - 03:06
This will do, thanks!
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.
