|
Tje opposite appears to be happening
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 |
| All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com