Formula query?

[Steve]

Hi could anyone please help with this formula/

=IF(AND(K10="Standard",AH>0),1,0)+AI103590

The formula works fine as is but I now have three different additional
standards

KStandard
WStandard
SStandard

so I would like the formula to work if either of three above are
mentioned in K10.

Is there a wildcard I can put in front of Standard that would pick up
all three types? I have tried ?Standard but i get an error.

Any help appreciated

Thanks

Steve

 

[Claus Busch]

Hi Stece,

Am Tue, 15 May 2012 17:53:53 +0100 schrieb Steve:

=IF(AND(K10="Standard",AH>0),1,0)+AI103590

The formula works fine as is but I now have three different additional
standards

KStandard
WStandard
SStandard

so I would like the formula to work if either of three above are
mentioned in K10.

try:
=IF(AND(COUNTIF(K10,"*Standard")=1,AH10>0),1,0)
or
=IF(AND(RIGHT(K10,8)="Standard",AH10>0),1,0)
or
=IF(AND(OR(K10="KStandard",K10="SStandard",K10="Wstandard"),AH10>0),1,0)


Regards
Claus Busch

 

[joeu2004]

=IF(AND(K10="Standard",AH>0),1,0)+AI103590
The formula works fine as is but I now have three
different additional standards
KStandard
WStandard
SStandard
so I would like the formula to work if either of three
above are mentioned in K10.

One way (among many):

=AND(OR(K10={"KStandard","WStandard","SStandard"}),AH10>0)+AI103590

Note that you really do not an IF express that returns 1 or 0 becuase TRUE
and FALSE are converted to 1 and 0 respectively when combined
arithmetically.

Also note that I assume "AH>0" was a typo. I changed it to AH10>0.

 

[cubbybear3]

This might also work, but as joeu2004 pointed out, I will assume the
"AH>0" should be ""AH10>0".
And since your IF statement has NO "value if false", I am just using
the values True/False.

=if(and(upper(mid(trim(K10),2,8))="STANDARD",AH10>0),"True","False")

 

[Steve]

Claus thank you so much for your help, that worked perfectly

Much apreciated.

Steve

 

[Steve]

Sorry I didnt see your reply

Thanks, much appreciated

steve

 

[Steve]

Sorry I didnt see your reply

Thanks, much appreciated

steve