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

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

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

Thank U if you can help.
Pat

Perhaps

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

--

HTH

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


"Pat" wrote in message
...
The following formula should only return a result if K19 contains "QS"

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

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

Thank U if you can help.
Pat

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
N1110. 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 wrote:

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

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

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

Thank U if you can help.
Pat

Hi gentlemen,

This solution is close to what I am after.
=IF(AND($B111=CHAR(252),$N1110,$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

"Jerry W. Lewis" wrote in message
...
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
N1110. 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 wrote:

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

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

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

Thank U if you can help.
Pat

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

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

Cheers
Pat


"Pat" wrote in message news:%23C5y2hnGFHA
...
Hi gentlemen,

This solution is close to what I am after.
=IF(AND($B111=CHAR(252),$N1110,$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

"Jerry W. Lewis" wrote in message
...
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
N1110. 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 wrote:

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

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

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

Thank U if you can help.
Pat

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),$N1110,ISNUMBER(FIND("QS" ,$K$19))),$N111,0)

--

HTH

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


"Pat" wrote in message
...
Hi gentlemen,

This solution is close to what I am after.
=IF(AND($B111=CHAR(252),$N1110,$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

"Jerry W. Lewis" wrote in message
...
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
N1110. 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 wrote:

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

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

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

Thank U if you can help.
Pat