# Return 1 Or Zero Based On Cell Value

• Hi,

I have the following scenario

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

Howdy!

Try this copied down:

=(K8="Correct")*OR(L8="Correct",ISBLANK(L8))

Richard

• 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.

## Files

• Re: Formula Based On Multiple Criteria

You had a circular ref in the formula - see the attached which shows how it should have been entered.

Richard

## Files

• 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,

[COLOR="blue"][SIZE="4"][FONT="Verdana"]AAE[/FONT][/SIZE][/COLOR]
[COLOR="blue"]----------------------------------------------------[/COLOR]

## Participate now!

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