Return 1 Or Zero Based On Cell Value

    Hi,


    I have the following scenario


    Answer 1 Answer 2 Score
    Correct Correct 1
    Correct 1



    I need a formula in the "score" column to be based on the following criteria:


    a) If both answer 1 and answer 2 is "Correct" , then column "Score" should populate 1 . If not, it should populate 0.


    b) In some cases, answer 2 is blank- no answer is needed in answer 2. If answer 1 is "correct" and answer 2 is blank, then the "Score" should be 1. If not is should be 0.


    Currently I ma using the following formula , but it doesnt work.


    =IF(K8&L8="Correct",0,1)


    Appreciate assistance.

    Re: Formula Based On Multiple Criteria


    Hi Richard,


    I tried the formula but it does not populate 1 or zero. I am attaching an example.


    On cell H8, the answer should be 1 ( if both answer 1 and answer 2 is "correct")


    In cell H40 the answer should be 0 ( if answer 1 is "correct" and answer 2 is blank)


    Appreciate ur help.

    Re: Formula Based On Multiple Criteria


    Thank you Richard. That worked. But can you explain how the formula returns 1 and 0 when I dont see any 1 or 0 in the formula ?

    Re: Formula Based On Multiple Criteria


    Hi Kumara,


    The formula evaluates whether the contents of the cell are "TRUE" or "FALSE" and assigns a value of either 1 (true) or 0 (false).


    Thus, if K8 contains "Correct", the formula (K8="Correct") equates to TRUE (value of 1)


    Because L8 can either have "Correct" or be blank, this part of the formula (OR(L8="Correct",ISBLANK(L8)) equates to either TRUE (1) OR FALSE (0). Blank = 0.


    Try this:


    In cell A1 enter: Correct
    In cell B1 enter: =A1="Correct"
    In cell C1 enter: =1*B1


    Delete A1, but leave B1 and C1 as is and you will see the value in C1 change from 1 to 0.


    HTH,

Participate now!

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

Register Yourself Login