Getting 0 to equal 1

[Guest]

I've tried the following formula to get a score of 1 if zero is entered in
cells 31, 32, 33. This formula does not produce the correct results. Help!!

=IF(AND(C31=0,C32=0,C33=0),1,"")

 

[Bernard Liengme]

If all cells have value of 1, then formula should return 1.
Are the values really 0, or are they calculations that give a near zero
answers. Try =C31=0, do you get TRUE?
best wishes

 

[Don Guillett]

Perhaps your zero is derived from a formula where it does not really = zero?

 

[Guest]

That's correct, the cells have formulas in them. Is there a way to write the
formula to take that into consideration?

 

[Gord Dibben]

What does it produce for you?

On my Excel 2002 it produces 1 when the three cells are zero(0) or blank.


Gord Dibben Excel MVP

 

[Don Guillett]

incorporate the use of INT (integer)

=IF(AND(INT(C31)=0,C32=0,C33=0),1,"")

=INT(SUM(C31:C32))

 

[Guest]

It produces a blank. The problem may be related to the fact that the three
cells have formulas that result in zero.

 

[JE McGimpsey]

Or, if your functions return valid data < 0:


=IF(AND(ROUND(C31,n)=0,ROUND(C32,n)=0,ROUND(C33,n)=0),1,"")

where n is the number of decimal places to round to to get the required
granularity (n = 1 to 15)

 

[Gord Dibben]

As others have pointed out, the formula results are not exactly zero.

Gord

 

[Guest]

Thanks all for your suggestions. Maybe my problem is that I've broken out
the scores according to the criteria for each of four scenarios. Ultimately,
all I need is one answer, ranging from 0 to 3. I know that this is possible,
but I just don't know how to write a formula that works.

Scenario 1: If 11, 11A, and 11B=4, score = 3
=IF(AND(C17=4,C23=4,C29=4),3,"")

Scenario 2: If 11 AND 11A=3 OR 11b=3, score = 2
=IF(OR(AND(C16=3,C22=3),C28=3),2,"")

Scenario 3: If 11=1, score 0
=IF(AND(C14=1),0,"")

Scenario 4: All others score 11 = 1
=IF(AND(ROUND(C31,n)=0,ROUND(C32,n)=0,ROUND(C33,n)=0),1,"")