Getting 0 to equal 1
 

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

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

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"soxn4n04" wrote in message
...
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,"")

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

--
Don Guillett
SalesAid Software

"soxn4n04" wrote in message
...
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,"")

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"soxn4n04" wrote in message
...
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,"")

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



On Mon, 29 Nov 2004 12:21:02 -0800, "soxn4n04"
wrote:

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

incorporate the use of INT (integer)

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

=INT(SUM(C31:C32))
--
Don Guillett
SalesAid Software

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

"Don Guillett" wrote:

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

--
Don Guillett
SalesAid Software

"soxn4n04" wrote in message
...
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,"")

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

"Gord Dibben" wrote:

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



On Mon, 29 Nov 2004 12:21:02 -0800, "soxn4n04"
wrote:

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

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)




In article ,
"Don Guillett" wrote:

incorporate the use of INT (integer)

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

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

Gord

On Mon, 29 Nov 2004 13:57:03 -0800, "soxn4n04"
wrote:

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

"Gord Dibben" wrote:

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



On Mon, 29 Nov 2004 12:21:02 -0800, "soxn4n04"
wrote:

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

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




"soxn4n04" wrote:

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