• 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

Write an IF formula|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Write an IF formula|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…Write an IF formula
sp_PrintTopic sp_TopicIcon
Write an IF formula
Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
1
August 22, 2018 - 1:00 pm
sp_Permalink sp_Print

Hi,

In Chapter 12_Data.xlsx workbook attached below , like to learn how to write If formula for Q. 3 on Sheet 1 from Data on OMT_DATA

Mitul

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
2
August 22, 2018 - 8:57 pm
sp_Permalink sp_Print

Hello Mitul,

It seems to me you are doing some training, so instead of giving you the answer, please do try to write an IF formula and then let's see if you need any help.

Br,
Anders

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
August 26, 2018 - 5:01 pm
sp_Permalink sp_Print

Hi Mitul,

Any progress with the formula?

/Anders

Avatar
MITUL PARIKH
Member
Members
Level 0
Forum Posts: 68
Member Since:
December 8, 2017
sp_UserOfflineSmall Offline
4
August 31, 2018 - 12:03 am
sp_Permalink sp_Print

                            Hello Anders;

                               Yes, there is Progress with IF formula.

                                     In workbook attached,  in Column H,  there is 

                            =IF(AND(OR(D8="Web",D8 = "Email"),AND(G8<=17,G8>17)),"Work","Home") for hours greater than 17  and it gives me correct 
                                  output which is home , and  for 

                                      Work output there is    =IF(AND(OR(D9="Web",D9 = "Email"),OR(G9<=17,G9>17)),"Work","Home"), so I like to learn can this
                                        formula  be same as "home" output formula ?  I tried few times, but cannot figure it out.  Can you Please help me ?

                                Thank you very much.

                               Have a great day.

 

                     

 

                             

 

 

             

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
August 31, 2018 - 4:16 am
sp_Permalink sp_Print sp_EditHistory

Hello Mitul,

I don't understand why you included column D in your formula.
The question is: "Do our customers shop at work (most likely between 8:00 and 17:00) or at home?"
To answer that question you only need to know at what time the order was made, not how.

One other thing that needs be pointed out here is how to work with date and time.
I don't know who have created a custom number format for the time data in column G, but even though the time in
column G is shown as hours by using custom number format, it does not mean that the value in the cells are just
in hours. As I try to show in attached file, in columns I, J and K I have added some extra controls.

In I8 I am checking if the data in G8 is equal to 22 or not, using following formula:
=IF(G8=22,"G8 is 22","G8 is not 22")
As you can see, the result is that the data in cell G8 is not equal to 22, even though 22 is shown.

In J8 I have take the time value from cell G8 and convert it to just hours, using following formula:
=HOUR(G8)
As you can see, the value shown is 22.

In K8 I run the same check as I did in I8, just checking the value in cell J8 instead.
=IF(J8=22,"J8 is 22","J8 is not 22")
And as you can see, I now get that the value in cell J8 is equal to 22.

In following cells down to row 14 I am doing another check.
In cells I9 to I14 I am checking if the values in cells G9 to G14 is greater than (gt) or less than (lt) 17.
=IF(G9>17,"G"&ROW(G9)&" is gt 17","G"&ROW(G9)&" is lt 17")
The result for all cells is that all the data is less than 17, even though one row is showing 22.

In cells K9 to K14 I do the same check but now against the cells in column J, which shows the hours.
=IF(J9>17,"J"&ROW(G9)&" is gt 17","J"&ROW(J9)&" is lt 17")
And now we see that in cell K14 it is now showing that the value in cell J14 is greater than 17.

What I am trying to point out here is that you need to ensure that you work with correct data.

A hint, when you have correct hour data, check if the hour is less than 8 OR greater than 17 (you can of course
check if the hour is greater than 7 and less than 18). If you don't want to create an extra hour column, you can
of course have the hour conversion within your formula.

Good luck.

/Anders

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
September 4, 2018 - 4:12 am
sp_Permalink sp_Print sp_EditHistory

Hello Mitul,

The formula you need to use is: =IF(OR(HOUR(G8)<8,HOUR(G8)>17),"Home","Work")

Br,
Anders

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Philip Treacy, Jack Brett, Natasha Smith
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
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Forum Stats:
Groups: 3
Forums: 24
Topics: 6211
Posts: 27239

 

Member Stats:
Guest Posters: 49
Members: 31891
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.