Question description:
This user has given permission to use the problem statement for this
blog.
Can someone review my my formula logic in the yellow cells? It's pretty self explanatory, just IF and COUNT functions sorting data. Let me know if you have any questions!
Solved by E. L. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/08/2018 - 05:50
Hi
Excelchat Expert
12/08/2018 - 05:50
Welcome to Got it Pro
Excelchat Expert
12/08/2018 - 05:50
Please share the data and tell more about the requirement so that i can help you better
User
12/08/2018 - 05:50
attaching the file!
[Uploaded an Excel file]
Excelchat Expert
12/08/2018 - 05:53
Which formula you want me to review
Excelchat Expert
12/08/2018 - 05:53
Please tell a bit about the requirement
User
12/08/2018 - 05:55
Ieom_plan_type2 dictates if the contract is a Contract
Subscription
Utility
User
12/08/2018 - 05:56
All of the formulas in the yellow rows are what I need reviewed based on the descriptions in column B
Excelchat Expert
12/08/2018 - 05:56
which cell is it?
User
12/08/2018 - 05:56
Sheet - Top X Column M:M
User
12/08/2018 - 05:57
Sheet Top X has all of the data being sorted
User
12/08/2018 - 05:57
Each Customer has 1 Account ID
User
12/08/2018 - 05:57
And Column C original_year_month is the Billing period
Excelchat Expert
12/08/2018 - 05:58
Yes i can see the formula
Excelchat Expert
12/08/2018 - 05:58
it is counting column C from Top X based on column L being contract in 7th row
Excelchat Expert
12/08/2018 - 05:58
Similarly it is doing for others
User
12/08/2018 - 05:59
Correct
Excelchat Expert
12/08/2018 - 05:59
Then it is finding % in row 14 and others
User
12/08/2018 - 05:59
I just wanted a second set of eyes to review if the logic is correct. It's for a board meeting soon
Excelchat Expert
12/08/2018 - 06:00
in row 36, it is for <1K
Excelchat Expert
12/08/2018 - 06:00
then why have you used >1000
User
12/08/2018 - 06:01
Good catch! That's the mistakes I'm looking for lol
Excelchat Expert
12/08/2018 - 06:02
Similarly in row 52 check there as well
Excelchat Expert
12/08/2018 - 06:03
Others seem to be fine to be
Excelchat Expert
12/08/2018 - 06:03
Let me know if i should specifically look at anything else
Excelchat Expert
12/08/2018 - 06:03
Please let me know if this session helps you
Excelchat Expert
12/08/2018 - 06:03
Looking forward to good rating and reviews from you
User
12/08/2018 - 06:05
Very Helpful! I'm also having difficulty with the logic for a question on the Top X sheet Column Q
Excelchat Expert
12/08/2018 - 06:05
where exactly?
Excelchat Expert
12/08/2018 - 06:06
I dont see a question or a logic there
User
12/08/2018 - 06:06
Q185 I'm looking to see how many customers stayed in the same Tenure from month to month
User
12/08/2018 - 06:06
Column E is the Tenure Rank the Customer is currently in
Excelchat Expert
12/08/2018 - 06:07
so you want how many account IDs were in different tenures?
User
12/08/2018 - 06:08
Count how many account ID were in the same tenure from month to month
Excelchat Expert
12/08/2018 - 06:08
okay, let me build the formula
User
12/08/2018 - 06:08
Thank you!
Excelchat Expert
12/08/2018 - 06:14
Sharing this excel
[Uploaded an Excel file]
Excelchat Expert
12/08/2018 - 06:14
I have done for row 185
Excelchat Expert
12/08/2018 - 06:14
Similarly we can do for others as well
Excelchat Expert
12/08/2018 - 06:15
Have a look and let me know if it helps you out
User
12/08/2018 - 06:15
looking now
Excelchat Expert
12/08/2018 - 06:16
Sure
Excelchat Expert
12/08/2018 - 06:16
For Q186 you can use this
Excelchat Expert
12/08/2018 - 06:16
=COUNTIFS($E$4:$E$238732,">10",$E$4:$E$238732,"<=20",$C$4:$C$238732,Q$2)/COUNT($C$5:$C$238732)
Excelchat Expert
12/08/2018 - 06:16
and then drag right
Excelchat Expert
12/08/2018 - 06:17
Hope this will help you in building for all other rows
User
12/08/2018 - 06:17
Awesome! Yah, I should be able to expand on this.
Excelchat Expert
12/08/2018 - 06:18
Great
Excelchat Expert
12/08/2018 - 06:18
Please do give good rating and reviews when you end the session
User
12/08/2018 - 06:18
Would the logic be different if I am comparing the last 2 and 3 months?
Excelchat Expert
12/08/2018 - 06:18
It really means a lot
Excelchat Expert
12/08/2018 - 06:19
I guess not, logic would be same but i am not sure what that means
Excelchat Expert
12/08/2018 - 06:19
We generally have a policy of 1 ques per session, hope you understand
Excelchat Expert
12/08/2018 - 06:19
If there are multiple questions in a session, we would request to raise another session for that
Excelchat Expert
12/08/2018 - 06:19
I hope that is fine with you
User
12/08/2018 - 06:20
THat's fine. Thank you so much! I will give you 5 Stars plus a good review
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.
