+ Reply to Thread
Results 1 to 5 of 5

Excel calculates the same statement in different ways while using the IF function

  1. #1
    Registered User
    Join Date
    08-18-2016
    Location
    Pune
    MS-Off Ver
    2016
    Posts
    3

    Excel calculates the same statement in different ways while using the IF function

    In the sheet below, I am using the IF function to determine whether the number in Column C (Variable) falls between the two numbers mentioned in Column A and Column B.

    I have entered the formula in Column D that displays the result while Column E shows the formula that I have used.

    Excel Question.PNG


    If I use the 'less than' symbol (<) in the IF function - A2<C2<B2 - I get the result as False
    If I use the 'greater than' symbol (>) in the IF function - B3>C3>A3 - I get the result as True

    If my basic math concepts are clear, A2<C2<B2 and B2>C2>A2 are exactly the same and they should give me the same result.

    Note: I can solve this by using IF(AND or by including multiple IF conditions, I am just curious to know why excel processes these two conditions differently.


    Please ignore the Image attached below. Not sure why it is showing up even after I removed the attachment.
    Attached Images Attached Images
    Last edited by Fred Price; 08-18-2016 at 03:51 PM. Reason: Older Attachment included

  2. #2
    Registered User
    Join Date
    01-27-2016
    Location
    Austin, TX
    MS-Off Ver
    Office 2010
    Posts
    6

    Re: Excel calculates the same statement in different ways while using the IF function

    Your math concept is correct, but Excel doesn't like multiple logic operators in one spot. If you use the formulas =AND(B44<D44,D44<C44) & =AND(C45>D45,D45>B45) respectively, you will get TRUE/FALSE responses without needing the IF statements or the "TRUE","FALSE" in the expression.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Excel calculates the same statement in different ways while using the IF function

    Order of operations: operations of equivalent priority are evaluated left to right. So (follow along in the Evaluate formula tool, if you desire)
    A1<C1<B1 is evaluated from left to right so
    A1<C1 evaluates to TRUE
    TRUE<B1 evaluates to FALSE because boolean values are larger than numbers

    Similarly for B1>C1>A1
    B1>C1 evaluates to TRUE
    TRUE>A1 evaluates to TRUE because boolean values are larger than numbers

    I am not sure where it is documented how Excel compares Boolean values to numbers. My testing suggests that the boolean values TRUE and FALSE are always greater than any number, which is what we are seeing in your example statement.

    In short, A1>B1>C1 is not the same thing as is B1 between A1 and C1. Use of the AND() function is necessary to correctly test for "between".
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    08-18-2016
    Location
    Pune
    MS-Off Ver
    2016
    Posts
    3

    Re: Excel calculates the same statement in different ways while using the IF function

    Quote Originally Posted by chazmo View Post
    Your math concept is correct, but Excel doesn't like multiple logic operators in one spot. If you use the formulas =AND(B44<D44,D44<C44) & =AND(C45>D45,D45>B45) respectively, you will get TRUE/FALSE responses without needing the IF statements or the "TRUE","FALSE" in the expression.
    Thank you Chazmo. I need different result than "True" and "False" in the output cell which necessitates the IF function to be used. Using the IF-AND combination or multiple IF functions does solve the problem, I am surprised to see that A<B<C does not mean 'B' between A and C as per excel's way of looking at it.

  5. #5
    Registered User
    Join Date
    08-18-2016
    Location
    Pune
    MS-Off Ver
    2016
    Posts
    3

    Re: Excel calculates the same statement in different ways while using the IF function

    Thank you MrShorty.

    That gives me a lot of clarity on how excel is processing the A1<C1<B1 statement.

    My curiosity was peaking when the first formula with the 'less than' operator didn't work while the one with 'greater than' worked when they are essentially the same thing.

    It is clear that in both scenarios, irrespective of what numbers are entered into column A, B and C - the results would not change. The first result would always be FALSE while the second would always be TRUE.

+ 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. Function that calculates payback period won't work
    By smoothopia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-13-2014, 03:19 PM
  2. Replies: 16
    Last Post: 06-19-2013, 12:11 AM
  3. Replies: 3
    Last Post: 04-06-2013, 11:04 PM
  4. [SOLVED] How to use the SUMIFS function so it calculates the right values
    By stojko89 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-16-2013, 06:54 AM
  5. Need a function that calculates time between 2 dates
    By RawisTheGameHhH in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-30-2006, 08:54 PM
  6. [SOLVED] edate function:calculates days
    By I Cruz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2006, 03:50 PM
  7. [SOLVED] percentiles?-How does excel calculates the percentiles worksheet function?
    By Agnes Goris in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-14-2005, 11:05 AM
  8. [SOLVED] If function that calculates
    By Ernie [email protected] in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-28-2005, 03:06 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