+ Reply to Thread
Results 1 to 6 of 6

TRUNC and MOD results return FALSE even when true

  1. #1
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    43

    TRUNC and MOD results return FALSE even when true

    I am trying to set up conditional formatting on all rows that have a number column A ending in .2

    I have two possible formulas to check the applicable cell for number ending in .2. However both formulas have the same problem. They return 0.2, but the formula =[RESULT]=0.2 returns FALSE.

    Formula 1: =A1-TRUNC(A1)
    Formula 2: =MOD(A2,1)

    Any ideas what is wrong and what will work right?
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,202

    Re: TRUNC and MOD results return FALSE even when true

    Try this formula,

    =MOD(10*A1,10)=2
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: TRUNC and MOD results return FALSE even when true

    formula 1 = 0.200000000000003 so it is not equal 0.2
    formula 2 = 0.200000000000003

    try: =ROUND(A1-TRUNC(A1),1)=0.2

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,101

    Re: TRUNC and MOD results return FALSE even when true

    Any ideas what is wrong and what will work right?
    Standard computer round off error. If you format B1 and B2 as scientific with 15 decimal places, you will see that both formulas result in 2.000000000000030E-01 which is not exactly equal to 0.2. The error is basically caused by the way computers do arithmetic as binary numbers, and most decimal fractions (even terminating decimals like 0.2) are not terminating decimals when converted to binary.

    MarvinP's solution works by forcing the arithmetic to work on integers only (which are exactly represented in binary) which can help with binary round off error. An interesting feature of my mentors work is to never test for exactly equal to. He would have formulated the test as ABS(result-0.2)<1e-8. Others would test ROUND(result,4)=0.2

    There are a lot of different solutions for this kind of problem. The main thing is to be aware of the possibility of rounding error and structure your programming to account for it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    02-20-2013
    Location
    Los Angeles, California
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: TRUNC and MOD results return FALSE even when true

    Thanks for the help; this was making me nuts! I'll keep the possibility of binary rounding errors in mind in the future.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: TRUNC and MOD results return FALSE even when true

    You are welcome
    Thanks for feedback and mark thread solved

+ 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] want results as true or false
    By abubaniyan in forum Excel General
    Replies: 3
    Last Post: 12-10-2014, 06:33 AM
  2. How to get an AND function to return back results other than "True and False"
    By bunnyfrostr in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-05-2014, 05:10 PM
  3. Replies: 14
    Last Post: 06-27-2012, 04:26 PM
  4. Replies: 3
    Last Post: 07-15-2006, 05:20 AM
  5. Function to return True/False if all are validated as True by ISNU
    By Tetsuya Oguma in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2006, 06:30 AM
  6. [SOLVED] My If statement results in False when actually true
    By papa jonah in forum Excel General
    Replies: 7
    Last Post: 03-17-2005, 08:06 PM
  7. [SOLVED] Adding True False Results
    By Arla M in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-27-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