 # Yes No calculations - automatically

• Hello,

I’m trying to put together a spreadsheet that calculates rates based on certain answers to yes or no questions. In the example that I’ve attached, there are two columns that can have a Y or N answer. If the first column is Y, then there should be a Y automatically placed in column 3.

Any Yes answer in column 1 gets an automatic Y in column 3. The problem is if there is a No in column 1 and a Yes in column 2. This is a valid exception and should also get an automatic yes in column 3 (see yellow row). Any No response in Column 1 gets an automatic N in column 3.

This spreadsheet may eventually have 200 rows.

Any help in getting this to work will be greatly appreciated.

Thanks,
Wattage
:thanx:

## Files

• Re: Yes No calculations - automatically

Try putting this into B4 and copy down for each Row.

Code
``=IF(AND(B4="n",C4="n"),"n","y")``

Is this what you are looking for?
Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont. Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... _______________________________________________

• Re: Yes No calculations - automatically

Ger,

I appreciate the effort. But "=If(And(B4="n",C4="n"),"n","y")" did not work. I need a formunla for cell D4.

I'll try to simplify the responses I'm looking for based on Col#1 and Col#2 answers. Column #3 would calculate a Y or N based on Col1 or Col2 answers:

Col#1 Col#2 Col#3
y + n = y

n + y = y

y + blank = y

Thanks,
Wattage

• Re: Yes No calculations - automatically

This shouldnt be that difficult, but I think you might have contradicted yourself - can you confirm the following?

Col 1 Col 2 Col 3
y y y
y n y
y *blank* y
n y y
n n n
n *blank* n

(Presume there will always be y or n in Col1 ?)

If this is the case, put the following in D4 and copy down.

Code
``=IF(AND(B4="n",C4<>"y"),"n","y")``

In summary, it appears to me the only time you want an n in column 3 is when col 1 = n and column 2 <> y

Thanks
Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont. Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... _______________________________________________

• Re: Yes No calculations - automatically

Ger,

Thanks, your new formula worked great!! I apreciate it!!

The formula, when copied into the rest of the spreadsheet, leaves a "y" in the cell regardless if there are any "y or n" in the preceding cells. As I am counting the 'Ys and Ns," this will lead to a false percentage in my rate for cells that are blank.

Is there a way to have anything other than a Y or N as a place holder?

Thanks,
Wattage

• Re: Yes No calculations - automatically

Try this :

Code
``=IF(AND(B4="",C4=""),"",IF(AND(B4="n",C4<>"y"),"n","y"))``
• Re: Yes No calculations - automatically

Hi wattage

Did you try something like this

• Re: Yes No calculations - automatically

Hi Wattage - glad to be of help... it did occur to me both cells could be blank - it just didnt make sense that this would happen... i figured (incorrectly) that there would be at least an 'n' in the first column. Thought made a fool of me again and I shouldnt have assumed.

Not to worry anyway, I think Greg.com's solution should work. I havent tested it, but it looks good. I'd say Jetted also has it nailed, but again, havent tested.

Happy xmas. (or holidays....)

Ger

_______________________________________________
There are 10 types of people in the world. Those that understand Binary and those that dont. Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... _______________________________________________

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!