Author Topic: Formula help using IF combined with OR  (Read 2159 times)

Tags:
  • All members
  • Posts: 405
Formula help using IF combined with OR
« on: 09 Oct 2016, 16:20 »
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?


  • Élite
  • Posts: 3698
  • Gender: Male
*
Re: Formula help using IF combined with OR
« Reply #1 on: 09 Oct 2016, 16:43 »
hi
IF(OR(silk_form_=P,silk_form_1=F),0,silk_form_1)

mcbee
Please read the following  disclaimer with regards to the information you may request and obtain on our forum. This specifically concerns trigger files and various instructions as to how to implement a strategy.

  • All members
  • Posts: 405
Re: Formula help using IF combined with OR
« Reply #2 on: 10 Oct 2016, 08:33 »
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)

  • All members
  • Posts: 405
Re: Formula help using IF combined with OR
« Reply #3 on: 11 Oct 2016, 10:01 »
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?

  • All members
  • Posts: 405
Re: Formula help using IF combined with OR
« Reply #4 on: 11 Oct 2016, 11:55 »
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

  • Élite
  • Posts: 3698
  • Gender: Male
*
Re: Formula help using IF combined with OR
« Reply #5 on: 11 Oct 2016, 12:11 »
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
Please read the following  disclaimer with regards to the information you may request and obtain on our forum. This specifically concerns trigger files and various instructions as to how to implement a strategy.

  • All members
  • Posts: 405
Re: Formula help using IF combined with OR
« Reply #6 on: 11 Oct 2016, 12:19 »
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!

  • All members
  • Posts: 405
Re: Formula help using IF combined with OR
« Reply #7 on: 11 Oct 2016, 12:33 »
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.

 

Please note, BetFair is seems to be currently OFFLINE