Question description:
This user has given permission to use the problem statement for this
blog.
I need to formulate a cell where it will show different values. for example if d2=0 i need e2 to show N. But the range of d2 could be anywhere from 0-60
Solved by I. U. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/02/2018 - 07:08
Hello! Welcome to Got It Pro-Excel.
User
07/02/2018 - 07:08
Hello
Excelchat Expert
07/02/2018 - 07:08
Hi
Excelchat Expert
07/02/2018 - 07:08
'm so glad to assist you today
User
07/02/2018 - 07:08
Im very grateful for your help
Excelchat Expert
07/02/2018 - 07:09
Thank you :)
User
07/02/2018 - 07:09
shall I paste what i have onto the sheet?
Excelchat Expert
07/02/2018 - 07:09
Can you share some part of your file to support the issue posted above?
Excelchat Expert
07/02/2018 - 07:09
Yes please
User
07/02/2018 - 07:10
ok
User
07/02/2018 - 07:10
so basically I am trying to assess my pupils scores on a test
Excelchat Expert
07/02/2018 - 07:11
okay
User
07/02/2018 - 07:11
So i calculate the raw score by adding paper one and two together
User
07/02/2018 - 07:11
then the result of that then relates to a scaled score
User
07/02/2018 - 07:11
so d2 currently equals 35
User
07/02/2018 - 07:12
I need the scaled score to represent
User
07/02/2018 - 07:12
the number next to 35 on this sheet
Excelchat Expert
07/02/2018 - 07:12
must be 99, right?
User
07/02/2018 - 07:12
Is this possible?
User
07/02/2018 - 07:13
yeah
Excelchat Expert
07/02/2018 - 07:13
Yes,
Excelchat Expert
07/02/2018 - 07:13
lease hold on, so I could create the formula for you
User
07/02/2018 - 07:13
thank you :)
Excelchat Expert
07/02/2018 - 07:13
My pleasure :)
User
07/02/2018 - 07:13
You are going to make me a very happy man if you do this!
Excelchat Expert
07/02/2018 - 07:14
Sure, thank you
User
07/02/2018 - 07:14
if tried this =IF(D2='2016 scaled score'!A2,'2016 scaled score'!B3)
Excelchat Expert
07/02/2018 - 07:15
Anyway, this is the formula for you
Excelchat Expert
07/02/2018 - 07:15
=vlookup(D2,Sheet2!$A$1:$B$62,2,false)
Excelchat Expert
07/02/2018 - 07:15
As shown at your E2
Excelchat Expert
07/02/2018 - 07:16
This method of using VLOOKUP function will give the best help to resolve the scenario like yours
Excelchat Expert
07/02/2018 - 07:17
Based on the formula, this means, we are looking the value of D2 at the table you've set that ranges from Sheet2!$A$1:$B$62
User
07/02/2018 - 07:18
I don't know how to apply that on to my workbook :(
Excelchat Expert
07/02/2018 - 07:18
Since the second column will be pulled out once the value is found, so let's put 2 as indicator at the formula
User
07/02/2018 - 07:18
ive typed =lookup(D2 then clicked the skeet
Excelchat Expert
07/02/2018 - 07:19
Simply follow the syntax of using the VLOOKUP formula, then based on our sample yoou can do the same way
User
07/02/2018 - 07:20
=vlookup(d2,'2016 scaled score'!A1:B62
Excelchat Expert
07/02/2018 - 07:20
=vlookup(D2,Sheet2!$A$1:$B$62,2,false)
Excelchat Expert
07/02/2018 - 07:21
Your formula is near it now.
User
07/02/2018 - 07:21
i think i have it
Excelchat Expert
07/02/2018 - 07:21
You must continue to put comma (,), then the number column like (2), then comma again, and then the word false
User
07/02/2018 - 07:21
but it has n/a for some
Excelchat Expert
07/02/2018 - 07:22
Yes, it will show n/a when the value is no longer found in the table
Excelchat Expert
07/02/2018 - 07:23
Have you got it?
User
07/02/2018 - 07:23
Its so strange
User
07/02/2018 - 07:23
as if i have a score it has done it
User
07/02/2018 - 07:24
but the 0's come up with n/a
Excelchat Expert
07/02/2018 - 07:24
By the way, incasethe value is not found or an error remarks like N/A, what comment you want to show
Excelchat Expert
07/02/2018 - 07:24
0 shows "N"
User
07/02/2018 - 07:25
it should do!
User
07/02/2018 - 07:25
but if i ad dnew values it just shows na
User
07/02/2018 - 07:25
thats what i have for one child
Excelchat Expert
07/02/2018 - 07:25
Oh, take a look at our sample now
User
07/02/2018 - 07:25
I would uplaod it but it has confidential details
Excelchat Expert
07/02/2018 - 07:26
I understand, and we value confidentiality
User
07/02/2018 - 07:26
Ahh!
User
07/02/2018 - 07:26
I think i know why
Excelchat Expert
07/02/2018 - 07:27
If D2 is blank then it will show N/A
User
07/02/2018 - 07:27
ah no
Excelchat Expert
07/02/2018 - 07:27
But if it shows specifically zero 0, then it shows "N" as set on your table
User
07/02/2018 - 07:27
thats what i'm getting
Excelchat Expert
07/02/2018 - 07:28
Which?
User
07/02/2018 - 07:28
it works on some but not on others
Excelchat Expert
07/02/2018 - 07:28
Why
Excelchat Expert
07/02/2018 - 07:29
Please don't forget to put a dollar ($) sign on the cell ranges
Excelchat Expert
07/02/2018 - 07:29
As shown on our sample inorder to make the range static
Excelchat Expert
07/02/2018 - 07:30
Look on how the dollar ($) sign being placed on this range Sheet2!$A$1:$B$62
User
07/02/2018 - 07:31
YAAAAAS
User
07/02/2018 - 07:31
we have lift off!
User
07/02/2018 - 07:31
Thank you so much
User
07/02/2018 - 07:31
Can I ask you one more thing
Excelchat Expert
07/02/2018 - 07:31
It will be having a big different if you remove the $ sign too just like this Sheet2!A1:B62
Excelchat Expert
07/02/2018 - 07:31
Sure
User
07/02/2018 - 07:32
If my raw score = 100 or greater I would like it to turn Green
Excelchat Expert
07/02/2018 - 07:32
Any more concern regardig this issue of ?
User
07/02/2018 - 07:32
If it less I would like it to be red
User
07/02/2018 - 07:32
Nope the formula is perfect now
Excelchat Expert
07/02/2018 - 07:32
Oh yeah, that will be possible by using formatting conditional
Excelchat Expert
07/02/2018 - 07:34
f you want mine, you can make another post for this topic of color coding so we could spend much time or the whole session to resolve yours since this requires more time again
User
07/02/2018 - 07:34
How do I do that?
User
07/02/2018 - 07:34
I think I will understand it quicker as I have done it before
Excelchat Expert
07/02/2018 - 07:34
Simply make a post again just like you did here to have a session
User
07/02/2018 - 07:35
ok! so end the session?
Excelchat Expert
07/02/2018 - 07:36
When everything is fine with us on this topic, simply click "End Session" , then you can make another post again regarding the color coding of your data.
User
07/02/2018 - 07:36
Ive managed it!
Excelchat Expert
07/02/2018 - 07:36
Only if this works for you
User
07/02/2018 - 07:36
Thank you so much for your help
Excelchat Expert
07/02/2018 - 07:36
Great :)
User
07/02/2018 - 07:36
This is an amazing service
Excelchat Expert
07/02/2018 - 07:37
My pleasure
Excelchat Expert
07/02/2018 - 07:37
Thank you too for coming
User
07/02/2018 - 07:37
Lets hope I can transfer this to my other sheets haha
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.
