Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Decimal test (2000)

    Many of our complex journal entries are created in huge excel sheets and sometimes the creators generate 3-decimal results. Our nit-picking master computer does not like 3-digit decimals. Rejects entry. As one possible approach to warn our users of this problem, I have a total-debits and total credits test wherein I multiply each total by 100, turn it into text, and see if there is then any value greater than zero to the right of the decimal point. If so, a message is displayed "decimal problem" See the attached workbook with the draft test formula. Here is the issue:
    When the test total DOES contain a "problem" number, the test works. When the test total contains an OK 2-decimal number, I get a #Value message. BUT only in the "combined test" formula. The "workup" level formulas returns the correct "No decimal problem" result. So, why doesn't the combined one work as well?
    Any comments would be appreciated. I'm not sure this is the most efficient way to do this, either. So other than formatting the worksheet columns to show >2 decimals, which would give everyone headaches, perhaps there is a more elegant solution. This is the right forum to find it. Thank you.

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Decimal test (2000)

    Hi Torquemada,

    The following formula will tell you how many decimal places exist in a field, regardless of whether they are visible:
    =IF(A3="","",LEN(A3)*(1-ISERROR(SEARCH(".",A3)))-IF(ISERROR(SEARCH(".",A3)),0,SEARCH(".",A3)))
    If you put that into cell C#, for example, and copy it down, it will automatically identify how many decimals are in each cell. Alternatively, you could put it into a conditional format to highlight a cell if it has more than 2 decimals. For example, in A3, you'd use:
    =IF(A3="","",LEN(A3)*(1-ISERROR(SEARCH(".",A3)))-IF(ISERROR(SEARCH(".",A3)),0,SEARCH(".",A3)))>2
    as the formula in Conditional Format. Set the conditional format, then copy cell A3's format to the rest of the range to be tested.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimal test (2000)

    I still see an issue with "significant figures" as I mentioned in <post#=283190>post 283190</post#> from a related question. Excel "ignores" trailing zeroes unless you change the displayed format. There is no way to signify them as significant or not. Excel always works with 15 significant digits (when you round you just make more "trailing zeroes"

    Steve

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal test (2000)

    The earlier observation on significant figures is why I changed it to a text object, found the decimal, and then did a conversion back to Value for that portion to the right of the decimal place. Maybe the value function, in converting that right-of-decimal text back to a value (to be tested against 0) is the problem.
    To approach it the rounding way, which might be better because the preparer wouldn't have to do anything, I shall attempt to write a macro. So I want to add a rounding function, and, while I'm at it, then check to see that it is not a negative number (another catastrophe for the Nit-Picker) and that there is not a number in the other (credit or debit) column (ie, one number per line) . All suggestions appreciated. I will give it a try and post what I come up with. Thanks for your comments.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Decimal test (2000)

    The mega formula you use:
    =IF(VALUE(MID(A3,(FIND(".",TEXT(($A$3*100),"000000 0000.000"))+1),5))>0,"Decimal Problem","No decimal problem")
    Should be:
    =IF(VALUE(MID(TEXT(A3*100,"0000000000.000"),(FIND( ".",TEXT(A3*100,"0000000000.000"))+1),5))>0,"Decim al problem","No decimal problem")
    If you don't want the error. You are getting the wrong string in yours. If it is correct the MID part yields a "null string" which has no "value".

    You could also use something like:
    =IF(A3*100-INT(A3*100)>0.000001,"Decimal Problem","No decimal problem")
    I used the "0.000001" to get rid of some of the rounding error. Make it bigger or smaller as desired for sensitivity

    Steve

Posting Permissions

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