Tje opposite appears to be happening

[Pat]

The following formula should only return a result if K19 contains "QS"

=IF($B111=CHAR(252),$N111,IF(AND($N111>0,$K$19="QS"),0,$N111))

The opposite appears to be happening, why should this be?

Thank U if you can help.
Pat

 

[Bob Phillips]

Perhaps

=IF(AND($B111=CHAR(252),$N111>0,$K$19="QS"),$N111,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jerry W. Lewis]

Break it down,

If B111=CHAR(252), then your formula returns N111 regardless of K19

If B111<>CHAR(252), then your formula will return 0 if both K19="QS" and
N111>0. Otherwise it will return N111.

If your only criteria is that K19="QS" then the formula should be

=IF($K$19="QS",$N111,0)

Also note that $K$19="QS" means that K19 is exactly equal to "QS" if you
only want K19 to contain QS (anywhere within the value) then you would use
ISNUMBER(FIND("QS",$K$19))

Jerry

 

[Pat]

Hi gentlemen,

This solution is close to what I am after.
=IF(AND($B111=CHAR(252),$N111>0,$K$19="QS"),$N111,0))

The only difficulty with it is that it will not return the value from N111
if "QS" is in K19

Pat

 

[Pat]

I have managed to pin-point and fix the problem. Here it is:

=IF($B111=CHAR(252),$N111,IF(AND($N111>0,$K$19="QS"),$N111,0))

Cheers
Pat

 

[Bob Phillips]

That is exactly what it does, but it also requires CHAR(252) in B111 and
N111 to be greater than 0.

Or do you mean if K19 is say "abcQSxyz", if so then use

=IF(AND($B111=CHAR(252),$N111>0,ISNUMBER(FIND("QS",$K$19))),$N111,0)

--

HTH

RP
(remove nothere from the email address if mailing direct)