This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I am trying to create a spreadsheet using the If function to turn yes, no answer to numbers.
For example column D already has data validation for a yes, no drop down option. I am trying to get column E to show different value for a yes or no option. Currently using =if(D2="Yes", 1,0) Hope is that if I select drop down yes it will show 1 or if I pick no or if there is no value it would be zero.
Currently with the =if(D2="Yes", 1,0) it results in a 0 appearing and does not change to 1 if yes is input in D2. As well if I add a space after "Yes " it will show 1, but wont change to 0.
Was hoping for help or different ideas to get something similar.
Currently using Microsoft excel 2011 on Mac
Thank you,
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Go back and look at the source of your data validation list. Instead of {"Yes", "No"} it would seem you have ("Yes ", "No "} (note trailing space(s)). Fix that.
Your formula could be shortened to,
=--(D2="Yes")