Finding Values With more than 2 decimal Places

[clane]

Hello,

I am using sumif to verify some numbers. These should all be dollar
values and thus should not contain and values past 2 decimal places
however when I use the sumif it returns values with long decimals? I
have been unable to find where these decimals exist visually and i
tried this formula to help me identify them

=IF(L18-(ROUND(L18,2))=0,0,"Help")
^ hopefully this would let me know if a decimal past 2 places is
present in a value but I still am unable to find them?


any help would be apreciated


Thanks

Chuck

 

[N Harkawat]

your formula looks OK and should provide you with the result you are
seeking.
Is your calculate option set to Automatic and not Manual?

 

[clane]

Yeah I thought it would work but I still cant seem to find any of th
decimals

 

[MrShorty]

How large of an error are we talking about? Could it be simply a resul
of "rounding error" because the computer has to work with binary number
and not decimal numbers

 

[clane]

OK so I found a way to find where the valuse should exist but I am still
unable to view them?

I trimmed down my data set to one version so sumif is unnecesary then
used

sum(A$1:A1) and copied it down then where ever the value changed I
figured there most be a decimal in that value I have the view so that
I can more decimals than I should need to in both columns but I still
can't view the actual decimal it just displays .0000000000

also the decimal change is in the 12th decimal #

thanks for your help

 

[clane]

1 more question

this file is imported to excel from a text file. Could thos
extraneous digits be added by that process? I was thinking tha
becasue now the text file puts dollar signs in front of dollar value
that might add some thing out in that 14th decimal point range? als
when I import these as text they only have 2 decimal points so I
thinking the number is added in that process somewhere?

any ideas

 

[MrShorty]

The error is in the 14th decimal place??? That is almost certainl
round-off error resulting from the computer using binary numbers.
found the following articles by searching on Google for rounding erro
binary:

http://www.cpearson.com/excel/rounding.htm
http://support.microsoft.com/default.aspx?scid=kb;en-us;78113

Here's something to try:
In and adjacent column, multiply the values in question by 100, t
eliminate fractions
Sum these values
Divide by 100 to get back to dollars and cents.
See if the extraneous digits are gone

 

[clane]

Thanks For your help I'm thinking that this has something to do wit
the formatting of the report as it has changed recently and the ne
format must be importing some style of formatting that when removed i
being read as a very small fraction

anyway thanks much

Chuc