Yes No calculations - automatically

Wattage · Dec 14th 2005

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:

**Ger Plante** · Dec 14th 2005

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

Wattage · Dec 15th 2005

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

**Ger Plante** · Dec 15th 2005

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

Wattage · Dec 19th 2005

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

Greg.Com · Dec 19th 2005

Re: Yes No calculations - automatically

Try this :

Code

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

jetted · Dec 19th 2005

Re: Yes No calculations - automatically

Hi wattage

Did you try something like this

Code

Sub count_y_n()
lastrow = Range("B65536").End(xlUp).Row
Range("b4").Select
For i = 4 To lastrow


Range("b" & i).Select
val1 = ActiveCell.Value
    If val1 = "y" Or val1 = "Y" Then
    val2 = ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Value
        If val2 = "n" Or val2 = "N" Or val2 = " " Then
        ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Select
        ActiveCell = "Y"
        Else
        ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Select
        ActiveCell = "Y"
        End If
    ActiveCell.Offset(rowOffset:=0, columnOffset:=-2).Select
    Else
    val2 = ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Value
        If val2 = "n" Or val2 = "N" Then
        ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Select
        ActiveCell = "N"
        Else
        ActiveCell.Offset(rowOffset:=0, columnOffset:=2).Select
        ActiveCell = "Y"
        End If
    ActiveCell.Offset(rowOffset:=0, columnOffset:=-2).Select
    End If
Next
End Sub

Display More

**Ger Plante** · Dec 19th 2005

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

Yes No calculations - automatically

Participate now!

Share

Users Viewing This Thread