Results 1 to 5 of 5
Thread: Decimal test (2000)

20030815, 03:44 #1
 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 3decimal results. Our nitpicking master computer does not like 3digit decimals. Rejects entry. As one possible approach to warn our users of this problem, I have a totaldebits 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 2decimal 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.

20030815, 04:09 #2
 Join Date
 May 2002
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 5,069
 Thanks
 2
 Thanked 420 Times in 349 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)*(1ISERROR(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)*(1ISERROR(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.
CheersCheers,
Paul Edstein
[MS MVP  Word]

20030815, 09:31 #3
 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

20030815, 11:47 #4
 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 rightofdecimal 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 NitPicker) 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.

20030815, 13:04 #5
 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*100INT(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