All solutions COLUMNS Expert Solution – Excel COLUMN Problems

Excel - COLUMN Function Problem - Expert Solution

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

I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/10/2018 - 05:58
Welcome to Excelchat, I see that your question is about Formulas.
Excelchat Expert 17/10/2018 - 05:58
I can help you with that problem, I'll be able to give you a solution and an explanation. Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 17/10/2018 - 05:58
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 17/10/2018 - 05:59
May you please share the data you have so I can help you with your concern? You can also paste a sample data on the document preview pane.
User 17/10/2018 - 05:59
yes
Excelchat Expert 17/10/2018 - 06:00
You can also upload the file by clicking on the paperclip icon at the bottom of this chat box
Excelchat Expert 17/10/2018 - 06:02
Based on what you've shared, you need to transform the rankings (3-6) into an equivalent letter.
User 17/10/2018 - 06:04
Yes
Excelchat Expert 17/10/2018 - 06:04
Where do we put the formula you need? In column C (DH grade)?
User 17/10/2018 - 06:05
under DH Grade column
Excelchat Expert 17/10/2018 - 06:05
Got it. I'm going to create the formula needed to get the output you want. This should take me less than 20 minutes. If we need more than 20 minutes, you'll be able to extend the session up to 60 total minutes.
Excelchat Expert 17/10/2018 - 06:05
I'll be working in the shared spreadsheet. When our session is over you'll be able to access the solution in your session history by downloading the Excel File.
User 17/10/2018 - 06:05
ok
Excelchat Expert 17/10/2018 - 06:08
I’m working on your solution. But don’t worry, I’ll be updating you throughout!
User 17/10/2018 - 06:09
ok
User 17/10/2018 - 06:15
Is anything happening?
Excelchat Expert 17/10/2018 - 06:15
Hi, yes I'm still working on your solution
User 17/10/2018 - 06:16
ok :)
Excelchat Expert 17/10/2018 - 06:18
Here's the solution to your problem: =IF(AND(A2="M",OR(B2=3,B2=4)),"A",if(AND(A2="F",OR(B2=3,B2=4)),"B",if(AND(A2="M",B2=5),"C",IF(AND(A2="F",B2=5),"D",IF(AND(A2="M",B2=6),"E","F")))))
Excelchat Expert 17/10/2018 - 06:18
Now that I've provided a solution, let me explain how this formula works.
Excelchat Expert 17/10/2018 - 06:18
The formula is a combination of Nested IF function, AND and OR logical functions.
Excelchat Expert 17/10/2018 - 06:19
This is the syntax for IF function: IF(logical_expression, value_if_true, value_if_false)
Excelchat Expert 17/10/2018 - 06:19
This formula returns one value if a logical expression is `TRUE` and another if it is `FALSE`.
Excelchat Expert 17/10/2018 - 06:20
AND(logical_expression1, logical_expression2)
Excelchat Expert 17/10/2018 - 06:20
AND function returns true if ALL of the provided arguments are logically true, and false if any of the provided arguments are logically false.
Excelchat Expert 17/10/2018 - 06:20
OR(logical_expression1, [logical_expression2])
Excelchat Expert 17/10/2018 - 06:21
Or function on the other hand returns true if ANY of the provided arguments are logically true, and false if all of the provided arguments are logically false. 
Excelchat Expert 17/10/2018 - 06:21
So on the first IF Formula I used: =IF(AND(A2="M",OR(B2=3,B2=4)),"A"
Excelchat Expert 17/10/2018 - 06:22
This means that if A2 = M and B2 = 3 or 4, then return A
Excelchat Expert 17/10/2018 - 06:23
otherwise, it will evaluate all the other IF functions I created in this formula with the corresponding combination of Sex and Grade.
Excelchat Expert 17/10/2018 - 06:23
Does this solution solve your problem?
User 17/10/2018 - 06:25
I think so, That formulae is put into theDH Grade column at C1 then when copied down it will look at all the data in each row? return the correct value?
Excelchat Expert 17/10/2018 - 06:25
Yes that is correct
Excelchat Expert 17/10/2018 - 06:26
Do you have any other questions about this problem and the solution?
User 17/10/2018 - 06:26
easy eh? lol Thanks I could have been here for days trying to phathom it out
User 17/10/2018 - 06:26
no more questions
Excelchat Expert 17/10/2018 - 06:26
You're very much welcome :)
Excelchat Expert 17/10/2018 - 06:26
Thanks for coming to Excelchat. Please feel free to leave any comments or give your kind feedback for our service. Have a nice day! :)
User 17/10/2018 - 06:27
will do
Excelchat Expert 17/10/2018 - 06:27
If you need additional help, please don't hesitate to contact us again.
Excelchat Expert 17/10/2018 - 06:27
Just a reminder, we are unable to end this session on our end until you do so please do not close this window without ending the session properly (Red X or End Session). Feel free to end this session as soon as you are ready for us to continue helping other customers. Thank you.
User 17/10/2018 - 06:28
where is the red x?
User 17/10/2018 - 06:28
found end session
Excelchat Expert 17/10/2018 - 06:28
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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc