1. ## 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. ## Re: Decimal test (2000)

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

3. ## 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. ## 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. ## 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
•