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.
