+ Reply to Thread
Results 1 to 17 of 17

if fUNCTION - returning false? what is wrong with my criteria

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    if fUNCTION - returning false? what is wrong with my criteria

    Hi guys

    excel shows false when I have the number 150 input. see the formule:

    =IF(J11>150,"2",IF(J11=150,"1",IF(J11<=149,"0")))


    For a cell where I have 150, excel doesn't show '1' it says; FALSE

    Please advise whyt?

    thank you!!
    Thanks,

    R.



  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: if fUNCTION - returning false? what is wrong with my criteria

    I've used your formula and for 150 I get a 1, but also, remove the quotes from around the numbers unless you are trying to return text.

    Data Range
    J
    K
    9
    1
    =IF(J11>150,2,IF(J11=150,1,IF(J11<=149,0)))
    10
    1
    =IF(J11>150,"2",IF(J11=150,"1",IF(J11<=149,"0")))
    11
    150

    Notice J9 is right aligned (a number) and J10 is left aligned (text)
    Last edited by jeffreybrown; 07-11-2019 at 07:49 AM.
    HTH
    Regards, Jeff

  3. #3
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Hi,

    I suspect that this is a rounding issue. Your formula does not have a contingency for J11 containing a value between 149 and 150. Therefore if the calculated value of J11 is 149.999999999 it will return FALSE.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,376

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Probably because the number is actually something between 149 and 150 that isn't catered for in your nested IFs - I'll bet that the number has decimals if you increase the decimal places.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: if fUNCTION - returning false? what is wrong with my criteria

    It's probably rounding. 149.999999999999 will show as FALSE because it doesn't match any of your conditions. If you use this instead, you will probably get a 0:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Edit: I really need to type faster - others keep beating me to it...!
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  6. #6
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Hi all

    thank you for help.

    It is J2 i have a problem with. I maybe think it is related to column I2 having a division formula

    Please see attached!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    01-25-2013
    Location
    near Philly, PA USA
    MS-Off Ver
    Excel 2019
    Posts
    178

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Rayted,
    Try using this formula (insert in J5):
    =IF(I5>150,2,IF(I5=150,1,0))

  8. #8
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: if fUNCTION - returning false? what is wrong with my criteria

    You're right. I3, for example, is actually 149.577940348903, so is less than 150.
    Two possible solutions:
    in I2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or

    in J2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Which you use depends on whether you might need the exact figures for 'Work per hour' for anything else.

    You could use ROUNDUP or ROUNDDOWN instead of ROUND if you want to.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Again, as has been pointed out, it has to do with the decimal places.

    How do you have a problem with J2? I2 = 251.23 which is greater than 150 so 2.

  10. #10
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Quote Originally Posted by Aardigspook View Post
    You're right. I3, for example, is actually 149.577940348903, so is less than 150.
    Two possible solutions:
    in I2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    or

    in J2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Which you use depends on whether you might need the exact figures for 'Work per hour' for anything else.

    You could use ROUNDUP or ROUNDDOWN instead of ROUND if you want to.
    thank you. the 2nd solution worked. rep added for everyone's help!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,376

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Thanks for the rep! Don't forget to add it to your notes so you remember next time it happens.

  12. #12
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: if fUNCTION - returning false? what is wrong with my criteria

    Quote Originally Posted by AliGW View Post
    Thanks for the rep! Don't forget to add it to your notes so you remember next time it happens.
    I will!

    thanks Ali!

  13. #13
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: if fUNCTION - returning false? what is wrong with my criteria

    I am adding another question to the thread (sorry I am not great with IF functions).

    Hi guys

    Think I am overlapping my criteria in an IF function which is not giving me right results.

    Please help:

    =IF(P2>99,"2",IF(P2<=98,"1",IF(P2<=96,"0")))

    I want to define if someone gets a score of 98 or below, their grade is 1.

    If they get 96 or below they get 0. But I am not seeing 0 in my results?

    pls advise

    many thanks!

  14. #14
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: if fUNCTION - returning false? what is wrong with my criteria

    The IF function works in the order you write it. This will check for <=96 before <=98:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You should also consider what you want for a score of 99, as exactly 99 isn't in your formula. Maybe make the first condition >=?
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    And don't forget about potential rounding errors as previously in the thread .

    ps thanks for the rep earlier.

  15. #15
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    831

    Re: if fUNCTION - returning false? what is wrong with my criteria

    thank you!
    Last edited by AliGW; 07-11-2019 at 10:44 AM. Reason: Please don't quote unnecessarily!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,376

    Re: if fUNCTION - returning false? what is wrong with my criteria

    This is the same issue as before.

    Are you sure that you UNDERSTAND the solutions given? If not, you must ask, or you'll keep making these mistakes.

  17. #17
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: if fUNCTION - returning false? what is wrong with my criteria

    @Ali - to be fair, the second problem was the logical order of the nested IF statements, so not the same as the first question.

    @rayted - thanks for the rep, again, and for marking the thread as Solved.
    You should maybe consider having a look at the Excel help files on functions which you want to use, like IF - they are generally quite good and will help you understand the logic behind each individual function. Sometimes just getting a solution given to you here will not help you understand why the solution works. As Ali has noted, you (like all of us) will only learn how to use them effectively if you understand them.
    (end of lecture, sorry!)

+ 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. [SOLVED] If isnumber search function returning false
    By hawkwolf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-14-2018, 02:36 PM
  2. Nested If function returning FALSE instead of number
    By erimhast in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2015, 07:47 PM
  3. Logical test in IF function returning TRUE when it should be FALSE
    By lvjeff in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2012, 05:13 PM
  4. Excel 2007 : Returning wrong value with IF function
    By bdinsdale in forum Excel General
    Replies: 3
    Last Post: 06-27-2011, 03:18 PM
  5. Excel 2007 : IF function returning wrong value
    By bdinsdale in forum Excel General
    Replies: 3
    Last Post: 06-27-2011, 03:00 PM
  6. IRR function is returning the wrong value
    By hackman2007 in forum Excel General
    Replies: 4
    Last Post: 11-07-2008, 11:24 PM
  7. if function returning false instead of my selection
    By kelleybr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-27-2006, 03:23 PM

Tags for this Thread

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