Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I'm creating a dynamic sentence that has multiple categories and metrics. The problem I have is the sentence needs to be more dynamic based on the categories and metrics for punctuation purposes. If there are 2 metrics in 1 category and 3 in a 2nd category I need the sentence to tell when to use a comma vs and &
Solved by Z. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 29/05/2018 - 10:04
Hello there, welcome to Got It Pro.
User 29/05/2018 - 10:05
Hi, did my problem make sense, kind of hard to describe
Excelchat Expert 29/05/2018 - 10:05
yes.
Excelchat Expert 29/05/2018 - 10:05
Not completely, but I get the idea.
User 29/05/2018 - 10:05
so here would be the example, I ran out of characters in my initial question
Excelchat Expert 29/05/2018 - 10:06
What confuses me is the difference between the category and metrics.
Excelchat Expert 29/05/2018 - 10:06
Sure.
Excelchat Expert 29/05/2018 - 10:06
Best if you could write it on the Excel Onilne panel on the middle of the screen.
User 29/05/2018 - 10:07
so say you are ranking metrics between cities. The metrics are "Sales", "Revenue", "Headcount" and those metrics are ranked into groups... the groups are "TOP10, Middle, BOT10"
User 29/05/2018 - 10:08
so in March, say NYC is Top 10 in sales and revenue, bottom 10 in heacount
User 29/05/2018 - 10:08
in April, say NYC is Top 10 in all 3
Excelchat Expert 29/05/2018 - 10:08
Alright.
User 29/05/2018 - 10:09
I need the sentence to know that for March it should be "NYC Top 10 in Sales and Revenue. Bottom 10 in Headcounts" omitting the "middle because there is no value"
Excelchat Expert 29/05/2018 - 10:09
Alright.
User 29/05/2018 - 10:09
on the April data it would read "NYC Top 10 in Sales, Revenue and headcount" and omits the other 2
Excelchat Expert 29/05/2018 - 10:10
Okay I get it.
Excelchat Expert 29/05/2018 - 10:10
Please allow me a few moments to present a solution.
User 29/05/2018 - 10:10
no problem... so assuming the name NYC is in cell c9... it would start with...
User 29/05/2018 - 10:11
=C9&" - Top 10 in"
Excelchat Expert 29/05/2018 - 10:11
Okay.
User 29/05/2018 - 10:11
the next part is what is holding me up
Excelchat Expert 29/05/2018 - 10:12
We'll figure it out in no time.
Excelchat Expert 29/05/2018 - 10:13
At first I would want you to take a look at the Excel Online panel on the middle of the screen.
Excelchat Expert 29/05/2018 - 10:14
Does the metrics and categories seem to be okay?
Excelchat Expert 29/05/2018 - 10:14
*Do
User 29/05/2018 - 10:14
so I did it a different way... looks like you are doing a grid for each metric... I did the city and then each metric... can I paste something into the excel sheet?
Excelchat Expert 29/05/2018 - 10:14
Sure.
Excelchat Expert 29/05/2018 - 10:14
That would be really helpful
User 29/05/2018 - 10:15
so pretend thats NYC.. and has some different metrics
User 29/05/2018 - 10:15
so I guess for the NYC example I would do...
Excelchat Expert 29/05/2018 - 10:16
Alright.
Excelchat Expert 29/05/2018 - 10:16
Thanks a lot for this example.
User 29/05/2018 - 10:17
no problem.. so what I did from there was basically
Excelchat Expert 29/05/2018 - 10:18
=F2&" - Top 10 in"
User 29/05/2018 - 10:20
so my thought was to do something like that and then for excel to look for any cells that arent blank
User 29/05/2018 - 10:22
still doesnt help me place a comma vs a &
Excelchat Expert 29/05/2018 - 10:22
will get to that in a moment.
Excelchat Expert 29/05/2018 - 10:22
WIll it be blank or a 'NO"
User 29/05/2018 - 10:23
should really be omitted, so I went with a blank
Excelchat Expert 29/05/2018 - 10:23
Alright.
Excelchat Expert 29/05/2018 - 10:25
Please have a look at E6.
Excelchat Expert 29/05/2018 - 10:25
Already figured out for an all positive scenario.
Excelchat Expert 29/05/2018 - 10:25
Now to work on the issues with , and &
User 29/05/2018 - 10:28
ok, so with what you have, I get it, I guess the only change would be that F2/G2/H2 should b e F2, F3, F4
User 29/05/2018 - 10:28
so if I can adjust what you did...
User 29/05/2018 - 10:29
there
Excelchat Expert 29/05/2018 - 10:29
On it.
User 29/05/2018 - 10:31
still with me? Just checking in, got to run in about 15 min
Excelchat Expert 29/05/2018 - 10:34
Yes.
Excelchat Expert 29/05/2018 - 10:34
It will be done by then.
User 29/05/2018 - 10:34
cool, thanks
Excelchat Expert 29/05/2018 - 10:36
You're welcome.
Excelchat Expert 29/05/2018 - 10:40
Could you please have a look now?
User 29/05/2018 - 10:42
so... close but I think its a row/colum thing again... also, it doesnt seem to know to use an & for what you have in there now
Excelchat Expert 29/05/2018 - 10:42
Yes, I missed it as well.
User 29/05/2018 - 10:42
because if its 2 of the 3 metrics it would be "metric 1 and metric 2"... vs all 3 being "metric 1, metric 2 and metric 3"
Excelchat Expert 29/05/2018 - 10:43
This really complicates things. But don't worry. I will sovle it.
Excelchat Expert 29/05/2018 - 10:43
*solve
Excelchat Expert 29/05/2018 - 10:43
If you can't be here I will email it to support and they will send it to you.
Excelchat Expert 29/05/2018 - 10:44
Check back with the Got It Pro website when you're back and you will find it.
User 29/05/2018 - 10:44
ok great, yeah got to run. Yeah this is complicated, im pretty good overall and I could make a giant nested if statement I just feel like theres a better way
User 29/05/2018 - 10:44
sounds good, thanks alot
Excelchat Expert 29/05/2018 - 10:44
No problem.
Excelchat Expert 29/05/2018 - 10:44
Thanks for using Got It Pro.
Excelchat Expert 29/05/2018 - 10:45
Have a good day.

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.