# Excel - IF Function Problem - Expert Solution

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
Excelchat Expert 14/08/2018 - 02:22
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
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
Excelchat Expert 14/08/2018 - 02:58
Did you get the file
Excelchat Expert 14/08/2018 - 02:58
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
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
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.