If statements

    Hi again,


    I'm doing these IF statements and I was wondering if there's a way to put a double if statement? Like


    If A1 = 0 and B1 = 0 then C1 = 0
    If A1 = 1 and B1 = 1 then C1 = 1
    If A1 = 1 and B1 = 0 then C1 = 0
    If A1 = 0 and B1 = 1 then C1 = 0
    If A1 and/or B1 = "" then C1 = ""


    Anyone?


    Thanks


    Sue

    Re: If statements


    Copy this statement and paste into C1


    =IF(AND(A1=0,B1 = 0),0,IF(AND(A1=1,B1 = 1),1,IF(AND(A1=1,B1 = 0),0,IF(AND(A1=0,B1 = 1),0,""))))


    Hope this helps

    Matt B

    Re: If statements


    Hi Guys!


    Ok..the first one worked but didn't work. It gave me blanks when B1 was blank, but zeros then A1 was blank. I C1 to be blank if there's a blank in A1 and/or B1. It also didn't give me a 1 in C1 when there was a 1 in A1 and 1 in B1.


    The second one didn't work also. No values came up in C1 at all.


    I've gone through and done it manually as time has run out on this..but I'd still appreciate learning how to do it using the IF statement. Thanks


    Sue

    Re: If statements


    I've attached a workbook with an example if you're interested.


    You might also consider using the sumproduct function if you are testing condition. I have found it very useful and saves a ton of time


    =sumproduct((column1="")*(column2=""))


    if both conditions are met, the formula return 1, if one is met, it returns 0, if neither are met, the formula returns 0.


    You can use this very effectively for large data dumps (17000+ rows by 20 columns).


    I have a spreadsheet that takes headcount by business unit and multiples it by the salary to calculate the salary by business unit.


    =sumproduct((Region="US")*(BU="X")*Salary)


    where Region, BU & Salary are named ranges on the table.


    Give it a try. This is one of the biggest time savers I have discovered (with the helps of this board and the excel hacks book).

    Re: If statements


    Quote

    Ok..the first one worked but didn't work. It gave me blanks when B1 was blank, but zeros then A1 was blank


    Your sample sheet did not provide an example of this, but my guess is that your column A cell was not blank. It may have displayed blank, but I'll bet there was a space or other non-printing character entered.


    See if the follwoing makes any difference. (The TRIM removes leading/trailing spaces)


    =IF(OR(LEN(TRIM(A1))=0,LEN(TRIM(B1))=0),"",IF(MIN(A1:B1)=0,0,1))

    Re: If statements


    Tom,


    I attached the workbook to help Suzanne but it looks like your post is addressed to me. Just wanted to let everyone know that the workbook I put out there is not the one she was having problems with.


    Thanks though...

    :thanx: Tony

    Re: If statements


    Hi Thom,


    Actually the blank scoring critera was the last line of my explanation..it was no tmeant to a sample "sheet" per se....rather was just the way I chose to describe the scoring I needed to do.


    That second one worked but didn't work. It fixed the blanks (and I think I know why the "blanks" weren't blanks before..I used an IF statement to change those fields from YES or NO answers to 1 and 0..and put if it was "" then keep it ""..which I"m now assuming puts in a space?.


    Anyways..it doesn't work because it does not give me a 1 in C1 when there is a 1 in A1 and A2.



    ASCalese..thanks for the sheet. I won't get the chance to try it today but I"ll let you know how it goes!


    Sue

    Re: If statements


    Actually, if your output cell is to have a "1" in it only if both A1 and B1 have 1's, then the following may work:


    =IF(AND(A1=1,B1=1),1,"Not Both Ones")


    Change the "Not Both Ones" to whatever you want.

Participate now!

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

Register Yourself Login