+ Reply to Thread
Results 1 to 4 of 4

How Excel calculates values for conditions

  1. #1
    Registered User
    Join Date
    08-13-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    How Excel calculates values for conditions

    I've encountered a very simple but irritating problem with Excel. It's to do with how Excel calculates functions of cell values, and how this relates to IF conditions.

    Its best to use an example.

    In cells A1, A2, A3 and A4 type the following values -

    20
    0.4
    0.1
    6

    In cell A5, calculate (A1*(A2-A3))-A4. This will evaluate to 0.
    In cells A6, B6 and C6, type A5<0, A5=0, A5>0.
    A6 should be FALSE, B6 should be TRUE, and C6 should be FALSE.

    BUT now try typing (A1* (A2-A3))-A4<0, (A1* (A2-A3))-A4=0, (A1* (A2-A3))-A4>0.

    This time the values will be FALSE, FALSE, TRUE. In other words, this time it evaluates the value as being >0, even though the two different methods condense to the same process.

    This is incredibly frustrating as my spreadsheet requires these sort of conditions to be evaluated accurately, but I am not sure how Excel works in terms of evaluating formulas. If I want to do more complicated formulas, but using the same <0, =0, >0 evaluation as above, do I have to get Excel to compute each separate bit of the formula to get it to evaluate the conditions correctly?

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

    Re: How Excel calculates values for conditions

    Not ideal I know, but you can get round it via

    =ROUND((A1*(A2-A3))-A4,10)<0

    I guess that a floating point imprecision is at work...
    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".

  3. #3
    Registered User
    Join Date
    08-13-2010
    Location
    Leeds, England
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: How Excel calculates values for conditions

    okay cheers, so does Excel stop at 10 decimals, i.e. it can't compute values more accurately than this due to how its programmed?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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