Comparing cell values using "IF"

Posted by Rossco on January 03, 2002 6:57 PM

I am attempting to create a formula that looks at two individual cell values (these will either have a value of "1" or be blank)

If both cells are 1, the result needs to be "1"

If only one cell displays "1" or if one or both are blank, the result needs to be "0"

=if(w4+z4=2,"1") this only works when both values are 1, if either are blank the result is #value (it needs to be a blank cell)

Posted by Scott on January 03, 2002 7:18 PM

You almost had it

You were almost there. Try this:

=if(w4+z4=2,1,"")

You just forgot to tell it what to do if the cells do not equal 2. In this case, it will just enter a blank cell (which is what "" is here). Also, if you want it to enter the number 1 if they equal 2, you do not need the quotation marks around the number (this is normally if you want to return Text).

Posted by Rossco on January 03, 2002 7:35 PM

Re: You almost had it

Hi Scott, thanks for your quick reply.
Unfortunately it now shows #value when both cells aren't "1".
It is correctly showing "1" when both reference cells are "1".
I triple checked that I copied your formula correctly.
Do you have any other suggestions?
Thanks.

: I am attempting to create a formula that looks at two individual cell values (these will either have a value of "1" or be blank)

Posted by Scott on January 03, 2002 8:11 PM

I almost had it....:) try this one

Sorry about that. It must be reading the cell as text to return that. Try this one:

=IF(AND(W4=1,Z4=1),1,"")

Unfortunately it now shows #value when both cells aren't "1". It is correctly showing "1" when both reference cells are "1". I triple checked that I copied your formula correctly. Do you have any other suggestions? Thanks. : You were almost there. Try this

Posted by Rossco on January 03, 2002 8:14 PM

Re: You almost had it

OK, got it figured finally.
The cells it was referencing used another formula that was resulting in a "" result. I have changed that to give a "0" result instead.
It seems excel was giving #value when trying to add ""+"".
I wonder if there is any other solution because I would prefer a blank cell rather than a "0" displayed in w4 and z4.
But thanks again for your help Scott. Unfortunately it now shows #value when both cells aren't "1". It is correctly showing "1" when both reference cells are "1". I triple checked that I copied your formula correctly. Do you have any other suggestions? Thanks. : You were almost there. Try this

Posted by Rossco on January 03, 2002 8:22 PM

It worked : )

Hey many thanks Scott.
That has done exactly what I want it to. Sorry about that. It must be reading the cell as text to return that. Try this one: =IF(AND(W4=1,Z4=1),1,"")

Posted by Tom Urtis on January 03, 2002 8:23 PM

Try this approach

Try this one:

=IF(AND(VALUE(W4)=1,VALUE(Z4)=1),1,"")

It should cover you in case a 1 in either cell is entered as text ('1), or is returned from a formula, or any other scenario. Or, if a formula is attempted testing for a "" blank cell.

You will probably also receive alternatives that will work just as well, this is just one way to cover your bases.

Tom Urtis

Posted by Tom Urtis on January 03, 2002 8:30 PM

Just a word of caution

This formula will not work if, for example, W4 contains a formula such as =V4, and V4 has a text numeral one in it, entered as '1.

If this will never be the case, then no problem, just raising a technical point.

Tom Urtis

Hey many thanks Scott. That has done exactly what I want it to. : Sorry about that. It must be reading the cell as text to return that. Try this one

Posted by Rossco on January 03, 2002 9:46 PM

Thanks Tom

The formula is working ok, but I thankyou for your input. This formula will not work if, for example, W4 contains a formula such as =V4, and V4 has a text numeral one in it, entered as '1. If this will never be the case, then no problem, just raising a technical point. Tom Urtis : Hey many thanks Scott. : That has done exactly what I want it to.