Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
decimal places within a formula | Excel Discussion (Misc queries) | |||
DECIMAL PLACES IN FORMULAS | Excel Worksheet Functions | |||
Changing default decimal places | Excel Discussion (Misc queries) | |||
goal seek wont calculate an accurate value past 3 decimal places | Excel Discussion (Misc queries) | |||
Entering numbers with variable decimal places. | Excel Worksheet Functions |