Log In
Register
Home
April 23, 2015
Offline
Hi everyone,
I am trying to write a formula to check for two text values, "NS" and "ND". So far all I have managed it to check for one value as per below. I am sure I should be using the OR function with IF to check for the second value but I cannot get the syntax right - could someone help me?
=IF(LEFT(H3,2)="NS","T40",H3)
Need the formula to check for both NS and ND and return T40 if true, return the value in H3 if false.
Many thanks!
Alan
April 23, 2015
Offline
Hi everyone,
I finally took the time to use the resources on the website and managed to figure this one out for myself - progress!
=IF(OR(LEFT(H18,2)="NS",LEFT(H18,2)="ND"),"T40",H18)
October 5, 2010
Offline
Good job Alan, well done 🙂
For anyone else reading this, this article might help
April 23, 2015
Offline
Thanks Phil - that is the article I read - very useful!
April 23, 2015
Offline
Hi there,
I am back on the "OR" trail, looking for help again. I have the formula below that does not work, (wrong syntax at least):
=IF(OR(MAX(G15:XFD15)>3.5,MIN(G15:XFD15)<-3.5,"VISIT"))
I am trying to write the formula to look at the values from G15:XFD15 and determine if any entry is greater than 3.5 or less than -3.5, if it is then return the text "VISIT", can someone help me get this right?
Many thanks,
Alan
VIP
Trusted Members
June 25, 2016
Offline
Try this
=IF(OR(MAX(G15:XFD15)>3.5,MIN(G15:XFD15)<-3.5),"VISIT","")
Sunny
April 23, 2015
Offline
Many thanks Sunny, it works perfectly of course! 🙂
So a very simple mistake from me in terms of the syntax, any advice on how best to solve that type of issue when writing formulas? I still seem to be using the trial and error approach which obviously doesn't work that well!
Cheers,
Alan
VIP
Trusted Members
June 25, 2016
Offline
Hi Alan
I suggest you split each formula into different cells first.
This will allow you to check each formula to see if it is returning the correct value and also help check for any syntax error.
Once you have determine that they are OK you can then combine the formulas together to create one mega formula.
Hope that makes sense. This is what I did to come up with the above formula.
Cheers
Sunny
April 23, 2015
Offline
Hi Sunny,
Many thanks for the insight. I should be able to work this out but just to check for the example formula above would you split into the following individual formula's in individual cells:
MAX statement
MIN statement
IF statement
then combine?
I like the concept of the approach, just looking to double check I am interpreting it correctly.
Cheers,
Alan
VIP
Trusted Members
June 25, 2016
Offline
Hi Alan
Yes you are right.
I check the inner most functions first i.e. MAX and MIN.
Then combine it with OR and recheck before combining it with the IF function.
Making sure that each function/combination is giving me the expected result.
Good luck.
Sunny
April 23, 2015
Offline
Hi Sunny,
Excellent! Many thanks for the advice and for catching the importance of validating the "OR" statement before the "IF" which I missed.
I will start employing this approach going forward, I guess we will see the success or otherwise by the number of posts I submit!
Cheers,
Alan
1 Guest(s)
