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?action=getinfo&userid=11865
View this thread: http://www.excelforum.com/showthread.php?threadid=381265

0
6/22/2005 3:26:32 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
468 Views

Similar Articles

[PageSpeed] 53

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" <clane.1r1ch9_1119456467.3759@excelforum-nospam.com> wrote in 
message news:clane.1r1ch9_1119456467.3759@excelforum-nospam.com...
>
> 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?action=getinfo&userid=11865
> View this thread: http://www.excelforum.com/showthread.php?threadid=381265
> 


0
N
6/22/2005 4:09:21 PM
Yeah I thought it would work but I still cant seem to find any of th
decimals

--
clan
-----------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1186
View this thread: http://www.excelforum.com/showthread.php?threadid=38126

0
6/22/2005 5:15:15 PM
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

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=38126

0
6/22/2005 5:30:27 PM
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?action=getinfo&userid=11865
View this thread: http://www.excelforum.com/showthread.php?threadid=381265

0
6/22/2005 5:32:33 PM
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

--
clan
-----------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1186
View this thread: http://www.excelforum.com/showthread.php?threadid=38126

0
6/22/2005 6:00:29 PM
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

--
MrShort
-----------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2218
View this thread: http://www.excelforum.com/showthread.php?threadid=38126

0
6/22/2005 6:30:47 PM
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

--
clan
-----------------------------------------------------------------------
clane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1186
View this thread: http://www.excelforum.com/showthread.php?threadid=38126

0
6/22/2005 7:37:19 PM
Reply:

Similar Artilces:

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Money Central Portfolio #2
My print function has gotten corupted. When I print it flips to WORD / format and prints in a format that is 4x normal. ...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Statements #2
How are statements sorted? Alphabetically by name or by account number? Does anybody know? -- Any help is appriciated, Deb Mine are done alphabetically by name. -- Elizabeth M. "Deb" <Deb@discussions.microsoft.com> wrote in message news:D58FEF32-7509-473B-A554-6A48E02697CF@microsoft.com... > How are statements sorted? Alphabetically by name or by account number? > Does > anybody know? > -- > Any help is appriciated, Deb ...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

Find and mark duplicates
If I have two columns with data, how do I compare these two columns to find all duplicates and put an X on a third colum next to all duplicates? Thanks. =if(a1=b1,"X","") "maxtrixx" <maxtrixx@discussions.microsoft.com> wrote in message news:139BC711-FAAC-4CD2-A533-CE49BA77291F@microsoft.com... > If I have two columns with data, how do I compare these two columns to > find > all duplicates and put an X on a third colum next to all duplicates? > Thanks. The problem is, I don't know which two cells to compare, I need to know if any o...

Sum amount if = 2 value's
I have a spreadsheet of payment types for which I want to sum the tota amount per type per month A B C Type Amount Month I'm able to get the total amount per type by usin =SUMIF(A:A,"TYPE",B:B), but can't work out how to get a total for eac type each month Somthing along these lines: =SUMIF((A:A,"TYPE",B:B)&C:C,"MONTH")) ???? Any idea's -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.ph...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

emails not showing up in deleted items until i do a find?
This just started happening a last week. A message comes in the Inbox and I click the delete key so delete it.. so far ok. then i go into the deleted items folder it's not visible. but if i do a find then they show up in the find box but still not in the folder. any ideas? this program makes me crazy sometimes *s* thanks starindy <anonymous@discussions.microsoft.com> wrote: > This just started happening a last week. A message comes > in the Inbox and I click the delete key so delete it.. so > far ok. then i go into the deleted items folder it's not > visible. b...

Outlook Express 6 Spell Check #2
I am using Outlook Express 6 on Windows XP, and I have a problem with spell check. When you send an email, it says "spell check has been haulted, do you want to send anyway" and if I ask it to spell check, it says an error has occured in spell check. All my spelling options are correctly set, so I don't know what the problem is. Kimberly McNabb <knmcnabb@hotmail.com> wrote: > I am using Outlook Express 6 on Windows XP, and I have a > problem with spell check. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Database #2
Hi I'm new to Outlook. I hear of an Outlook database in other threads. Is this synonymous with the ..pst or .ost files people mention? Does it corrupt often and if so are there tools to repair it? Regards Craig. There's a utility called ScanPST.EXE that will scan and attempt to repair the file. Yes, typically when a user says Outlook database they're referring to their PST file "Craig Roberts" <craigroberts@jkr.co.uk> wrote in message news:C4C8E141.211DE%craigroberts@jkr.co.uk... > Hi > > I'm new to Outlook. > > I hear of an Outlook d...

unique with 2 columns
I have 2 columns with duplicates in the first column and no duplicates in the second. I neeed to only see one value for each in the left and only one of the accounts from the right. ie: abc 1234 abc 12345 bcd 251 eft 600 eft 607 I would like to see either of the abc with the 1234 or 12345 I would like to see the bcd since it's individual with the 251 I would like to see either of the eft with the 600 or the 607 Use a totals query and one of the aggregate functions (First, Last, Min, or Max) on the...

host unreachable #2
hi, if i send a email with a wrong address or mispelled it, i don't get a message that the address, destination is wrong until 4 days later after being deletedj from the outgoi queue im using E5,5 sp4 thks Is that for all domains, even for Yahoo and Hotmail? Rick wrote: > hi, > > if i send a email with a wrong address or mispelled it, i > don't get a message that the address, destination is wrong > until 4 days later after being deletedj from the outgoi > queue > > im using E5,5 sp4 > > thks ...

Excel number formatting #2
I receive spreadsheets with separate columns of numbers and text. The problem is that the numbers column is not in number or general format (when sorting behaves like text). Is there a way to turn those columns into numbers (except stepping into each one separately)? When I just highlight the number in the cell and hit enter, the cell automatically becomes numeric (I'm looking for a more global solution). Thanks, A You can do this: 1. Type 1 (the number 1) into a blank cell. Highlight this, select Edit, Copy. Now highlight entire column(s) that you want changed to numeric, and sel...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

Compare each value in a range to each value in another range
I am looking for a macro that will return a comparison of each cell in a range to each cell in another range. example. the first range would have vales of 1,2,3 and the second range would have values of 5,6,7 Thus the macro should return 9 possible comparisons: 1 & 5, 1 & 6, 1 & 7, 2 & 5, 2 & 6, 2 & 7, 3 & 5, 3 & 6, 3 & 7, Can anybody help me with this?? Just hazarding some thoughts here .. Perhaps using formulas would suffice ? Example: Assume 1st range is A1:A3, 2nd range is B1:B3. Then Compare 1st range against 2nd range I...

Closing Excel #2
I have inherited an App desigend by an ex employee Sometimes the program leaves Excel in the task manager The programe uses various routines and calling various functions that define Excel sheets etc (badly named) I'm finding it hard to follow (my 1st experince of .NET) is there a way to KILL any Excel instence when exiting the program. I have come across some posts about GC (garbage collection) like this System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWsheet) oXLWsheet = Nothing 'System.Runtime.InteropServices.Marshal.ReleaseComObject(oXLWs) oXLWs = Nothing oBook.Clos...

hidden transactions #2
I have a problem with account balances: the balance for checking is way off on the hiome page than in the account register view. When I tried to find out why, I got some greyed out transaction when I went from bill schedule through right click to payee. This transaction cannot be changed or deleted, but in total all such transactions made up the difference in balance. Any suggestions? Oscar -- Elvart In microsoft.public.money, elvigor wrote: >I have a problem with account balances: the balance for checking is way off >on the hiome page than in the account register view. When I ...

Help!
We went live with 4 decimals on our functional currency and we now want to reduce them to 2 decimals. All of our transactions so far are in 4 decimals. Can anyone guide me into doing this? thank you Use the Change Decimal Places window to change the number of decimal places used for keeping quantity or currency amounts for items. You might need to complete this procedure if an item was set up improperly, or if you’re changing the units of measurement for an item. NOTE: If an item is on an unposted transaction, you can’t change its decimal place settings. Ensure you have a complete ba...

Finding and naming duplicates
Hi All, not sure if anyone can help with this. I have a column called VNTR24 which has 10+ digit numbers - these are not unique and we want to be able to identify people with matching VNTR24 numbers to cluster them. In excel I've used the following formula: =COUNTIF(A:A,A2)>1 to return in the next column a TRUE or FALSE based on whether two records are matched on VNTR24 number. I need to do the same in Access 2007, returning either a "TRUE" or "FALSE" if the records are clustered/matched on this VNTR24 or not in an UPDATE query!? Can anyone h...

I have individual place cards I want to print can anyone advise m
I am helping friends print their wedding place cards, they have been given the individual cards from the place where they are having the wedding, but we cannot get them to line up What are the size of the place cards? Are there more than one card on the page? Are you merging information? What version Publisher are you using? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Pauline" <Pauline@discussions.microsoft.com> wrote in message news:77E3682B-3926-410B-8012-2907CF63F37A@microsoft.com... >I am helping friend...

Stats/PivotTables #2
I have to produce stats from data exported as a CSV file from a piece of custom made software we use here. The data displayed in Excel looks like this: 01/04/2003 05:31 Dr JESSICA STROUDLEY 01/04/2003 08:56 Dr JESSICA STROUDLEY 01/04/2003 11:16 Dr JESSICA STROUDLEY 02/04/2003 11:42 Dr JESSICA STROUDLEY 02/04/2003 05:31 Dr JESSICA STROUDLEY 02/04/2003 08:56 Dr JESSICA STROUDLEY 03/04/2003 11:16 Dr JESSICA STROUDLEY 03/04/2003 11:42 Dr JESSICA STROUDLEY I have to produce a spreadsheet saying how many examinations were done by a particular doctor over a certain timespan. Each date li...