• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Using IF and OR |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Using IF and OR |General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Using IF and OR
sp_PrintTopic sp_TopicIcon
Using IF and OR
Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
1
September 24, 2019 - 11:29 pm
sp_Permalink sp_Print

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

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
2
September 25, 2019 - 1:13 am
sp_Permalink sp_Print

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)

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1517
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
September 25, 2019 - 9:44 am
sp_Permalink sp_Print sp_EditHistory

Good job Alan, well done 🙂

For anyone else reading this, this article might help

Excel IF AND OR Functions

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
4
September 25, 2019 - 11:01 pm
sp_Permalink sp_Print

Thanks Phil - that is the article I read - very useful!

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
5
October 9, 2019 - 11:03 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
6
October 10, 2019 - 2:21 am
sp_Permalink sp_Print

Try this

=IF(OR(MAX(G15:XFD15)>3.5,MIN(G15:XFD15)<-3.5),"VISIT","")

Sunny

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
7
October 10, 2019 - 1:35 pm
sp_Permalink sp_Print

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

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
8
October 10, 2019 - 6:45 pm
sp_Permalink sp_Print

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

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
9
October 10, 2019 - 10:59 pm
sp_Permalink sp_Print

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 

Avatar
SunnyKow
Puchong, Malaysia

VIP
Members


Trusted Members
Level 8
Forum Posts: 1432
Member Since:
June 25, 2016
sp_UserOfflineSmall Offline
10
October 11, 2019 - 2:04 am
sp_Permalink sp_Print

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

Avatar
Alan Ramsay
Member
Members
Level 0
Forum Posts: 79
Member Since:
April 23, 2015
sp_UserOfflineSmall Offline
11
October 11, 2019 - 9:58 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Anders Sehlstedt, RAQUEL ACION, Dana Friedt, Chandler Davis, gogy13, Brenda Richards, Mark Luke
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 202
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
Forum Stats:
Groups: 3
Forums: 24
Topics: 6218
Posts: 27267

 

Member Stats:
Guest Posters: 49
Members: 31901
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.