Question description:
This user has given permission to use the problem statement for this
blog.
I need to use the answer "None" in column A to put "0" in all corresponding column B cells
Solved by X. S. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
10/04/2018 - 02:34
yes sorry
Excelchat Expert
10/04/2018 - 02:34
Hello, welcome to ExcelChat!
User
10/04/2018 - 02:34
I was looking it up
User
10/04/2018 - 02:34
I'm using the 2016 version
Excelchat Expert
10/04/2018 - 02:35
I see! I'd like a brief rundown of your problem, if you don't mind.
User
10/04/2018 - 02:35
Sure
Excelchat Expert
10/04/2018 - 02:36
Correct me if I'm wrong: you want a "0" in B1 if I put "None" in A1?
User
10/04/2018 - 02:36
Yes
User
10/04/2018 - 02:36
exactly
Excelchat Expert
10/04/2018 - 02:37
Okay, we'll be using an IF formula to speed things up, especially if you plan on adding other stuff.
Excelchat Expert
10/04/2018 - 02:37
The usage of the IF formula is as follows:
User
10/04/2018 - 02:38
Thank you
Excelchat Expert
10/04/2018 - 02:38
=IF(conditional, value_if_true, value_if_false)
Excelchat Expert
10/04/2018 - 02:39
So for our example here, we'll be using A1="None" as the conditional
User
10/04/2018 - 02:39
Ok
Excelchat Expert
10/04/2018 - 02:40
We can see that if it's not None on the corresponding cell, it just spits out "FALSE"
Excelchat Expert
10/04/2018 - 02:41
So we set the value_if_false argument to blank to get a blank cell
Excelchat Expert
10/04/2018 - 02:41
Our final formula would be
Excelchat Expert
10/04/2018 - 02:41
=IF(A1="None",0, )
Excelchat Expert
10/04/2018 - 02:42
And you can pull it down the number of rows you need it.
Excelchat Expert
10/04/2018 - 02:42
Does this answer your problem?
User
10/04/2018 - 02:42
I only want to use "None" to put "0" in the corresponding cell, but not change the other answers that are already there. Is that possible?
User
10/04/2018 - 02:42
Yes, it does for the most part, I just have the above question
Excelchat Expert
10/04/2018 - 02:43
Oh! Hold on for a bit. I'll check something.
User
10/04/2018 - 02:43
Thank you!
Excelchat Expert
10/04/2018 - 02:46
So let me clear this: If A1 is "None", you want a 0 in B1. But if it's not "None", you want it to remain unchanged?
User
10/04/2018 - 02:46
Yes!
User
10/04/2018 - 02:48
Are you still there?
Excelchat Expert
10/04/2018 - 02:48
Yup!
User
10/04/2018 - 02:49
Sorry, I wasn't sure and didn't want the timer to run out
Excelchat Expert
10/04/2018 - 02:49
You might want to use the C column in this instance
Excelchat Expert
10/04/2018 - 02:50
If B has values which you'd rather keep
User
10/04/2018 - 02:51
well, there are other values in Column A besides "None" so I guess the above formula would work. I want all the "None" answers to become "0" in column B, but if it isn't "None" in column A to remain unchanged
User
10/04/2018 - 02:52
So I'm thinking maybe the above formula is actually what would work. Is that correct?
Excelchat Expert
10/04/2018 - 02:53
But that would delete all the data in Column B, with None in Column A or not
Excelchat Expert
10/04/2018 - 02:53
If we put
Excelchat Expert
10/04/2018 - 02:53
=IF(A1="None", 0,B1)
Excelchat Expert
10/04/2018 - 02:53
in column C, we can produce a column with both 0s and the data in column B
User
10/04/2018 - 02:55
Ok, so the only 2 answers in Column A are "none" and "yes
User
10/04/2018 - 02:55
so I would make a Column C to fill in 0 for only the "None" answers
User
10/04/2018 - 02:55
?
User
10/04/2018 - 02:55
And not to change the data already in Column B that corresponds to Column A
User
10/04/2018 - 02:55
Column A "Yes"
User
10/04/2018 - 02:56
The column C looks like it would work, how would I do that?
User
10/04/2018 - 03:03
Hi, are you still there?
User
10/04/2018 - 03:08
Hello?
User
10/04/2018 - 03:11
Did you leave the chat?
User
10/04/2018 - 03:13
Hello???
User
10/04/2018 - 03:15
Can I maybe put =IF(A1="Yes",$B$2) so that the corresponding cell for a "Yes" in column A remains unchanged, and then I can add 0 and pull it down so it only changes the "None" answers?
User
10/04/2018 - 03:18
You've left haven't you
User
10/04/2018 - 03:19
Wow, that's super shitty of you
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.