Having trouble with =IF formula
Excel VBA Tools from Andrew
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Having trouble with =IF formula

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Having trouble with =IF formula

     
    Hello All,
    Would like to see if someone can help me with the following issue:

    I am comparing the information typed into cells D61, 62, 63, 64, 65 to the static information in cells C61, 62, 63, 64, 65.

    This is how the static information appears in the C cells:
    C61 has 2:00
    C62 has 3:00
    C63 has 4:00
    C64 has 8:30
    C65 has 10:30

    I used =IF(D61<=C61,"YES","NO") in the equivalent E cells and copied it down to E65 so if the manually entered info in the D cells was equal to or less than the C cells I would get YES and if it was greater than I would get a NO.

    Works perfectly on all except when comparing D65 to C65. As long as anything between 10:00 and 10:30 is entered I get a YES but if anything else is entered I get a NO even when it is less than 10:30. All info is formatted as text.

  2. #2
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Sorry, that last sentence should read I get a NO even when anything less than 10:00 is entered. Again, 10:00 to 10:30 returns YES. From 9:59 back and 10:31 forward returns NO.

  3. #3
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Having trouble with =IF formula

    CLRead, Good afternoon.

    Are you sure that you formulas ask for <= instead = only?

    Take a look at your formula again.

    I hope it helps.

  4. #4
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Quote Originally Posted by Marcílio_Lobão View Post
    CLRead, Good afternoon.

    Are you sure that you formulas ask for <= instead = only?

    Take a look at your formula again.

    I hope it helps.
    Yes I believe they do. All five formulas are the same =IF(D""<=C"","YES","NO")

    All work as they should except for E65 comparing D65 and C65.

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,833
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Hi,

    The problem is, you said All info is formatted as Text, comparing Text with the > >= < <= expressions are Not going to work correctly.

  6. #6
    Board Regular
    Join Date
    Oct 2013
    Location
    Belo Horizonte, Brazil
    Posts
    695
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Having trouble with =IF formula

    CLRead,

    "...All info is formatted as text...."
    Perhaps the question is due to this fact.

    Do all cells in column C and column D have the same format?

    Save your spreadsheet template on a free website www.sendspace.com and place the download link here.

    So we can check what is happening.

    I hope it helps.

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Quote Originally Posted by jtakw View Post
    Hi,

    The problem is, you said All info is formatted as Text, comparing Text with the > >= < <= expressions are Not going to work correctly.
    I'm a little slow with formulas so bear with me.......how does the formula work with the other four cells when all five are all formatted the same?

  8. #8
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Quote Originally Posted by Marcílio_Lobão View Post
    CLRead,

    "...All info is formatted as text...."
    Perhaps the question is due to this fact.

    Do all cells in column C and column D have the same format?

    Save your spreadsheet template on a free website www.sendspace.com and place the download link here.

    So we can check what is happening.

    I hope it helps.
    I would love to do that but unfortunately the spreadsheet contains company information that is sensitive and can't be shared or altered.

  9. #9
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    2,833
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

    Quote Originally Posted by CLRead View Post
    I'm a little slow with formulas so bear with me.......how does the formula work with the other four cells when all five are all formatted the same?
    Hit and Miss. It didn't actually "Work", it just so happens it yielded the result you wanted by Luck.

    Basically, it's like =IF("THIS"<="THAT","YES","NO")
    Last edited by jtakw; Mar 16th, 2018 at 04:11 PM.

  10. #10
    New Member
    Join Date
    Mar 2018
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Having trouble with =IF formula

      
    So what would be the best formula to get the desired results on for all cells?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com