Excel - COLUMN Function Problem - Expert Solution

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
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
User 10/04/2018 - 02:36
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
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
User 10/04/2018 - 02:48
Are you still there?
Excelchat Expert 10/04/2018 - 02:48
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
User 10/04/2018 - 03:11
Did you leave the chat?
User 10/04/2018 - 03:13
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.

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
The Allstate Corporation
United Parcel Service
Dell Inc