Getting 0 to equal 1


G

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,"")
 
Ad

Advertisements

B

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
 
D

Don Guillett

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

Guest

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

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
 
D

Don Guillett

incorporate the use of INT (integer)

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

=INT(SUM(C31:C32))
 
Ad

Advertisements

G

Guest

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

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)
 
G

Gord Dibben

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

Gord
 
Ad

Advertisements

G

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,"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text to equal "0" 6
1 equals 3 2
Percentage equals 0 error 6
Conditional sum equals 0 4
=SUBSTITUTE(;0;;1) 6
find smallest number not equal to 0 2
Getting "0" into cell if "0" is there 3
equal to 1 cell above it 1

Top