Help to fix formula ="Yes"

The following formula is not doing what it is supposed to be doing:

=IF(AND(I87-J87>0,S87="Yes"),0,I87-J87)

A value should only be returned if S87="Yes"

What happens is a value is returned regardless if S87="Yes" or not. Anyone
know what is wrong here?

Thanks
Pat

Hi,
the "yes" in the formula needs to be double quoted...
the yes in cell s87 does not.

peter
>-----Original Message-----
>
>The following formula is not doing what it is supposed
to be doing:
>
>=IF(AND(I87-J87>0,S87="Yes"),0,I87-J87)
>
>A value should only be returned if S87="Yes"
>
>What happens is a value is returned regardless if
S87="Yes" or not. Anyone
>know what is wrong here?
>
>Thanks
>Pat
>
>
>.
>

Pat

The function as you have shown will always return a value (on the assumption the the both I87 and J87 are numeric values) but depending upon the values of I87 and J87, it returns a zero values in only 1 condition (when I87 > J87 AND S87 = "yes"). In all other cases, it returns the difference of I87 and J87.

It appears as if you want the returned value dependent only on the value of S87, in which case you need remove the dependency on the the values in I87 and J87 like so :=

=IF(S87="Yes"),0,I87-J87)

If this is not your question, may be then I have interpreted it incorrectly! Sorry!


Best regards



Deepak Agarwal

Peter,
What through the formula is that the cell had been custom formatted. I have
now changed the formula to:
=IF(AND(I87-J87>0,S87=1),0,I87-J87)
Thanks



"peter" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> the "yes" in the formula needs to be double quoted...
> the yes in cell s87 does not.
>
> peter
>>-----Original Message-----
>>
>>The following formula is not doing what it is supposed
> to be doing:
>>
>>=IF(AND(I87-J87>0,S87="Yes"),0,I87-J87)
>>
>>A value should only be returned if S87="Yes"
>>
>>What happens is a value is returned regardless if
> S87="Yes" or not. Anyone
>>know what is wrong here?
>>
>>Thanks
>>Pat
>>
>>
>>.
>>