Finding Values With more than 2 decimal Places
 

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


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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" wrote in
message ...

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


--
clane
------------------------------------------------------------------------
clane's Profile:
http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265

1 more question

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

any ideas?


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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

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

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


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=381265

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

anyway thanks much

Chuck


--
clane
------------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php...o&userid=11865
View this thread: http://www.excelforum.com/showthread...hreadid=381265