Formula help using IF combined with OR

Hi everyone

Just wondering if someone with better knowledge of formula could point me in the right direction. I'm trying to exclude certain characters from the recent form and I'm getting stuck getting the formula to work.

I'm trying to replace the P & F ('Pulled up' and 'Fallen' in the form identifiers) with a 0. Specifically, If silk_form_1 contains either a P or an F, I want silk_form_1 to return the number 0 rather than anything else.

my attempt so far:

IF(silk_form_1=P,0,silk_form_1) works correctly identifying the single letter P and replacing it with a 0 , but how do I make the formula look for P OR F, not just P on its own?

I've tried IF(silk_form_1=(P,F),0,silk_form_1) and also IF(silk_form_1=P,F,0,silk_form_1) but both those have the wrong syntax


Anyone got any ideas?

hi
IF(OR(silk_form_=P,silk_form_1=F),0,silk_form_1)

mcbee

Thanks Mcbee, much appreciated. 

There was a tiny syntax error with a 1 missing, but with that tweak it works great.

IF(OR(silk_form_1=P,silk_form_1=F),0,silk_form_1)

Hi Mcbee

Sorry, I spoke too soon!

Your syntax works great with letters, but for some reason, the 2 symbols that are used in the form strings, / and -, don't parse correctly.

I've included a screen grab to show the situation.  For this horse silk_form_3 is a / and silk_form_4 is a P. As you can see from the user variable viewer, the formula correctly identifies the P and replaces it with a 0, but it can't do the same with the /

any idea how to make the formula work with these symbols as well?

Answered my own question after a bit more experimenting:

IF(OR("silk_form_3"="P","silk_form_3"="/"),0,silk_form_3)

putting everything in quotes produces the correct answer

hi
i used this formula on a system that i created , giving points to a 1,2,3 and 4 in the silk form,for some reason or other the / had to have it's own IF because it would not work in a OR command.
this was set for the 3rd fav so remove the _3 or replace the 3 with your fav position.
(IF(r_3_silk_form_1=/,0,IF(OR(r_3_silk_form_1="-",r_3_silk_form_1="F",r_3_silk_form_1="P",r_3_silk_form_1="U",r_3_silk_form_1="B"),0,IF(r_3_silk_form_1=1,5,IF(r_3_silk_form_1=2,4,IF(r_3_silk_form_1=3,2,IF(r_3_silk_form_1=4,1,0)))))))

mcbee

Thanks for the new formula Mcbee, I will try adapting it.

What is weird is that I have just got one sticking point on my current formula that I've made, and it's a strange one. quotes work for / , but not for - !!!

screen grab shows this. silk_form_2 for this horse does not pick up the - but silk_form_5 does pick up the /

I've tried putting spaces before and after the - but that doesn't help

scratching my head on that one!

Quick update,

Your formula sorted out the problem with - entries as well so thanks for that.

Apologies about the multiple posts, but the forum only allows us to modify previous entries for a few minutes now, so I've had to add another post for this update.