Excel 2007 :: Changing Number To Add Decimal Places
Jan 30, 2013
I am using Excel 2007 and importing a list of coordinates. The coordinates are in a text file and separated by commas, so I'm opening the file as a delimitted file. All of the coordinates import correctly except one. The value is supposed to 52530.6372, but Excel is automatically changing it to 52530.6371999999
If I manually type a "2" to replace the "1999999", it changes it back. If I type "3" it stays as a 3.
I added a round function to force it to round to 4 decimal places and that seemed to work until I then tried using the number with text commands. When I used an ampersand to insert the number into a text string, the additional decimal places are back, even with a round function!
Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)
The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.
My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.
So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
Is there a way I can get excel (2007) to limit itself to 2 decimal places for all numbers entered? So effectively I don't want use the formulas TRUNC or ROUND (or any other formula) but I would like rather excel to behave like my calculator where I can limit the decimals to 2 no matter how many I input.
In other words, every time a input a number, excel will round it up automatically to 2 decimal places and the number available will have 2 decimals only. Is there any option hidden somewhere in the Excel options that I couldn't find?
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
I was trying to decrease the decimal places of the data figures that I'm currently working on my report, however, it keeps rounding-up the decimal numbers. I wish to keep the original numbers and just decrease the decimal places.
For example: The original figure is = 7260.12903225806 Upon decreasing the decimal figures to just 2, the result became = 7260.13
Is it possible for me to just have this result = 7260.12 instead of 7260.13?
I've tried using the TRUNC formula butit does not work if the 2nd decimal value is 0.
For example: The original figure is = 227161.905808985 Upon applying the formula, the result became = 227161.9 instead of 227161.90
Is there a way to format a cell based upon a condition? If the cell value is <1, I want to show two decimal places. If the cell is >1, I want to show zero decimal places. I tried to use the conditional formatting, but there is no option for this.
I need a function to find the number of decimal places of a certain number (in this specific case doubles)
The first solution would be something like this:
Code: 'returns the number of decimal places within a double Public Function getDecPlaces(inputNum As Double) As Long Dim ndx As Long ndx = InStr(1, inputNum, ".") If ndx > 0 Then getDecPlaces = Len$(CStr(inputNum)) - ndx End If End Function
But i feel there is likely a much better way of doing this..
If you look at the attached file in Column E line 47 you will see that it displays 373.97. However if you look in the formula bar it shows 373.9694. I am trying to make it so that the formula bar shows the same number as in the cell. Any idea on how to do this? I have tried everything I know how to do, which is not much.
Workbook with 21 sheets, first sheet is the summary sheet (named SUMMARY) taking all it's content from the other sheets, named from 1 to 20, which are all laid out the same, but contain different data.
My problem is that I want to get rid of decimal places that are being brought into the SUMMARY sheet
In sheets named 1 to 20, I am using the following formula in H23 to arrive at a figure =IFERROR(100/(B5/B23),0) in H23
B5 and B23 are always whole numbers on all sheets 1-20, result can be whole or contain decimal places which I round up in the cell to show no decimal places
On sheet 1, B5 is 200 and B23 is 5, giving 2.50 as the answer, which is right, I have H23 set as a number cell with no decimal places so it rounds up to 3 which is what appears in the H23 cell.
All of the above is correct and appearing as I want it on the sheet, the problem arises when I import into the SUMMARY sheet as follows:
On the SUMMARY sheet in cell B59 I want the following to appear 5 - 3 but am getting 5 - 2.5 using the following formula
='1'!B23&" - "&'1'!H23
The first part is fine as B23 will always be a whole number
How can I stop it from bringing in the 2.5 and get it to bring in the rounded up 3 into cell B59
I have cell B59 set as a number cell with no decimal places but it still appears as 2.5
Also when I correct it as it appears a lot on the summary sheet, how do I get the following to happen
='1'!$B$23&" - "&'1'!$H$23
if I fill the above across columns how to I get the two '1' s in the formula to become '2' then '3' as I fill across.
So how can it be that Excel calculates Pi()/90*90=Pi(); but Pi()/90+Pi()/90+Pi()/90......90 times is 0.000000000000033527. And why is the number zero out to 14 decimal places not being returned as 0?
I need to know exactly when a series of sequentially increasing radians = Pi(). The length of the series is dynamic and can be as few as 6 and as many as 120. And right now Excel is not cooperating.
Here is what excel thinks Pi()/90+Pi()/90+....90 times =
3.14159265358980000000
3.14159265358979000000 (this is how excel displays Pi() out to 20 decimal places)
So Excel thinks the two numbers are not equal to each other, and what could be more interesting is that Excel thinks the difference is
0.00000000000000355271
Even after copying and pasting both numbers as values out to 20 decimals. Excel still thinks the difference is 0.00000000000000355271
How is that possible?
I have Excel 2013. Is there some sort of bug here? I had posted earlier about major headaches when using natural log. Random cells keep getting forced to 14, 15, sometimes 16 or 17 decimals. No rhyme no reason. I can custom format every cell in worksheet to 2 decimals, and as soon as I hit F9... ############# all over the place. At least with Pi() the decimals are under control, but the math is not right. Pi()/x + Pi()/x.....x times = Pi().
I have a userform with 35 text boxes on which display data from a worksheet based on a selection made from a combobox.
My problem is that TextBox11 and TextBoxes 15-35 all need to show a value to 2 decimal places i.e 360.00.
I have found the following line of code:
Code:
For Each ctrl In Controls If TypeName(ctrl) = "TextBox" Then ctrl.Value = Format(ctrl.Value, ".00") Next ctrl
Which does the job but unfortunatley it applies it to every single textbox on the form (not just No's 11,15-35) which is a problem as some of the boxes contain dates so instead of 09/05/2012 i actually get 41038.00.
My question is how to I modifiy the above code (or is there an alternative code?) to only apply to the required textboxes?
I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
I have tried, through control panel - region settings etc, to change the default number of decimals to 0. I often use the Number format using the shortcut CTRL + SHIFT + 1 but then have to reduce the 2 decimals down.
I have recorded a macro to apply this kind of formatting but the disadvantage of this is that I cannot undo any actions before that point - which has gotten me into some trouble on a few occasions .
Is there a macro that I can use that adjusts the "Normal" cell style number format from General to Number (with thousand separator but 0 decimals) each time I open a new book? As merging cell styles is also quite a mundane task..
I was going to use the following to test that a number has no more than two decimal places,
Code:
If Int(ActiveCell.Offset(0, 8) * 100) ActiveCell.Offset(0, 8) * 100 Then However it is rather mysteringly failing when activecell = 16.99 or 17.99; on testing it appears that vba is evaluating Int(ActiveCell.Offset(0, 8) * 100) to equal 1698 or 1798
I am facing a problem with Excel's Concatenate function. I am trying to make a text string with numbers from a cell. =CONCATENATE( "price paid= ", D23). D23 is a numreric cell, formatted for one decimal place. However, the text produced by above function is showing me two decimal places of the number in D23.
I am creating a Daily Cash Count worksheet for a business to do their daily closeout paperwork. What I am trying to do is automatically apply decimal formatting to a cell. Examples, if they count 60 cents in nickles and enter 60 in the cell, it automatically converts it to .60. And if they count 7.50 cents in quarters and enter 750 in cell it automatically would convert it to 7.50.
I'm trying to work out a formula in excel which requires me to use Pi to over 30 decimal places. Excel will only let me have 30 decimal places of Pi whether I copy and paste it as a number or use its Pi function and even then seems to round up. Is there any way I can get more than 30 decimal places for this calculation? If not in excel then can anyone suggest another programme that may be capable of this? You can post here or contact me at [email=" Deleted by Jack in the UK[/EMAIL]
Is it possible to apply some kind of formatting to a range, that will force all numbers entered into that range to have the same number of decimal places as the cell with the maximum number of decimal places?
Everytime I enter a number, excel automatically converts it to a decimal number.
I type 1 in a cell (or formula box) Excel returns the value as 0.1
I think it may have to do with the FIX box highlighted to the bottom of the screen. (To the right side of the NUM lock). (I can't paste a screen dump to show the FIX box position). how to remove the FIX box, and/or change excel so that I can enter in numbers normally.
I am doing some calculations via Excel, and I have found that I get different results using a calculator. I know that by formatting the number, I can show as many or as little decimal places as I like in any cell, but can anyone tell me how many decimal places Excel actually uses when it's carrying out calculations? I have a feeling that the Excel results I'm getting might be more accurate than my calculator ones.
I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:
