+ Reply to Thread
Results 1 to 9 of 9

IF Statement to derive a TRUE or FALES formula

  1. #1
    Registered User
    Join Date
    05-02-2014
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    39

    Question IF Statement to derive a TRUE or FALES formula

    Hi All,

    Need help with a IF Statment - I have attached a example spreadsheet.

    What I am trying to do:
    - Col F: is deriving total mins using =(C2+D2)-(A2+B2) and using a custom format [m] to show me mins.
    - Col H: is where I want to calculate my 4 HR SLA.

    So to sum it up IF Col F is <= to 240 show me a 1. IF Col F is > then 240 then show me a 0.

    However this is where I am having problems the formuale I am using for Col H: =IF(F2<=240,1,0) which is not derving the correct value of 1 or 0. Can someone please shed some light on this for me. I am still new to excel and have a hit roadblock. Your assistance will be grealty appreciated.

    Regards,
    Ravi
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: IF Statement to derive a TRUE or FALES formula

    Try this.......

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    42,114

    Re: IF Statement to derive a TRUE or FALES formula

    Try this:

    =IF(F2<=0.166666667,1,0)

    This uses the underlying value of a custom formatted cell showing 240 minutes.
    Last edited by AliGW; 05-06-2014 at 01:35 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,682

    Re: IF Statement to derive a TRUE or FALES formula

    Or

    In H2 Cell

    =(VALUE(TEXT(F2,"[m]"))<=240)*1

    Drag it down...


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Registered User
    Join Date
    05-02-2014
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    39

    Re: IF Statement to derive a TRUE or FALES formula

    Hi Guys,

    Thanks for the assistance sktneer, AliGW and Sixthsense all three formula's suggested below works perfectly fine. Thanks again for you time and help greatly appreciated.

    Regards,
    Ravi

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: IF Statement to derive a TRUE or FALES formula

    You're welcome.

  7. #7
    Registered User
    Join Date
    05-02-2014
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    39

    Re: IF Statement to derive a TRUE or FALES formula

    Hi All,

    Quick question - one variable i had forgotten if the Cell has no value how do I show if i.e Col F has no value meaning a blank cell then for Col H to show blank. Sorry I should have stated as well.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,530

    Re: IF Statement to derive a TRUE or FALES formula

    Like this......

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-02-2014
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    39

    Re: IF Statement to derive a TRUE or FALES formula

    Thanks sktneer really appreciate your help. Regards, Ravu

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Derive formula from chart
    By hamidun in forum Excel Charting & Pivots
    Replies: 26
    Last Post: 04-29-2014, 09:12 AM
  2. Replies: 2
    Last Post: 03-04-2014, 06:31 PM
  3. Subtotal Formula that counts if statement is true
    By shelton_4 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-15-2013, 10:57 PM
  4. [SOLVED] cellcolor within formula not returning true statement
    By damo_uk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-30-2013, 10:20 AM
  5. derive formula from series of points
    By andyismilesaway in forum Excel General
    Replies: 1
    Last Post: 02-28-2007, 06:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1