Persistent Decimal Rounding?
Mar 20, 2013
My worksheet has a column full of numbers that are suffixed with "." and more numbers.
Example
Before After
135443.234 135443.23
456544.810 456544.81
435466.235 435466.24
654352.8 654352.80
I save my files as .txt. In the text files, the numbers appear preserved. But when they're opened in excel, they're rounded. If I format them as numbers with 3 decimals, the "#.8" becomes "#.800" which is no good. Oh, and the suffixes which end in a zero always end up losing the zero.
I've formatted my columns as general, number (with 3 decimals), and as text. That hasn't worked.
I've tried concatenating, separating the decimal from the suffix: =456544&"."&810
I've tried using the value formula to get get the cell value.
View 4 Replies
ADVERTISEMENT
Feb 11, 2013
How do I write a formula to round the decimal places in a number in set increments. For example;
a) 14.28 to the nearest 0.25 would result 14.25
b) 1.99 to the nearest 0.1 would result 2.0
c)2.97 to the nearest 0.25 would result 3.00
View 2 Replies
View Related
Mar 19, 2009
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
View 4 Replies
View Related
Jul 25, 2006
I have columns of geological data in number form which may have about 4 or 5 decimal points. I want to reduce them to 2 decimal points without rounding the numbers up. Is there a simple way to do this?
View 4 Replies
View Related
Sep 19, 2006
My formula is not rounding properly. Cells I4, J4 and K4 all filter from the results of cell I3 divided by 3. (e.g. 10/3 = 3.333) I was able to remove the decimals in cells I1:K3, but the between formulas (I4:K4) keeps adding the decimal back, therefore this results in errors to my chart.
View 4 Replies
View Related
Feb 1, 2009
I'm essentially creating a basic table and I have my first page created exactly how I want it. Header, footer with page numbers, and the main page consists of a table with borders, column headers, and every other row highlighted. I'm making the table for a small company to record the results of equipment tests that they have to run every morning. What I need it to do is to keep that format on every new page that gets activated. The users of this particular form that I'm making are not particularly computer savvy, so asking them to recreate the table everytime they need a new page would be asking quite a bit.
View 14 Replies
View Related
May 27, 2014
I have a Column of dates in this format: 5/19/2014 8:08:44 AM I am trying to get this to read 20140519 or yyyymmdd It doesn't matter what format I change to, it will not update unless I activate the cell and then tab or enter out of it. I have 3000 rows to convert. with this either by setting or VB Sub?
Excel 2010
View 3 Replies
View Related
Apr 21, 2009
In the sheet I am working on I have some numbers that represent todays activity. These numbers are volatile and reset when the date advances. I have a column on another tab that has all the days of the month as F4:F33
The results I want will go in the G4:G33 column.
C2 is Today() to avoid too many Today()s.
Active!J10 is the value I need in the G column.
Basically what I need is the value from Active!J10 to be copied to whichever cell corresponds to the date next to it in the G4:G33 and then stay there when the date advances
The formula I am using for the g range is :
=IF(F24=C2,Active!J10)
=IF(F25=C2,Active!J10)
=IF(F26=C2,Active!J10)
And so on down the column.
I dont mind the FALSES for future dates, but the current one changes to false upon the advancement of the date. Basically, I need a running log of the final numbers going down the G range and for those values to stay there when the date advances.
View 2 Replies
View Related
Jul 9, 2008
I am trying to round similar to Banker's Rounding or Scientific Rounding but I can't find a consistent formula that works perfect with decimals.
Using three decimal places for all the samples, I can get 0.0785 to round to 0.078 but 0.1785 wants to round to 0.179 instead of staying 0.078. Or 0.0005 will round to 0 but 0.5115 wants to round to 0.511 instead of 0.512.
Here is a list of sample numbers along with desired results:
.0785 should be .078
.5115 should be .512
.5035 should be .504
.0005 should be 0
.0025 should be .002
.0194 should be .019
.0195 should be .02
.0135 should be .014
.0115 should be .012
.8115 should be .812
I cannot find a formula which gives me all of these results. Here is a list of the formulas I have tried so far (NOTE: cell A2 is the working cell in my worksheet where I enter the number to be rounded)
1) =MROUND(A2,0.001)
3) =ROUND(A2,3)
4) =IF(ISERROR(IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOOR(A2,0.001))),0,IF(MOD(MID(A2,4,1),2)=1,CEILING(A2,0.001),FLOO R(A2,0.001)))
5) =EVEN(A2)
6) =ROUNDUP(A2,3)
7) =ROUNDDOWN(A2,3)
View 9 Replies
View Related
Aug 13, 2009
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:...
View 2 Replies
View Related
Aug 3, 2006
Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies
View Related
Jun 10, 2009
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.
View 4 Replies
View Related
Apr 15, 2012
I have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
View 7 Replies
View Related
Aug 8, 2008
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently:
=IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2))))
However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
View 2 Replies
View Related
Jun 10, 2007
I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies
View Related
Feb 27, 2014
I need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.
For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00
Lets say the number is in cell A1. What formula would do this?
View 3 Replies
View Related
Jul 23, 2009
What is the code to round up a range of numbers at certain decimal.
View 14 Replies
View Related
Jan 30, 2008
I have a sheet that i use for doing quotes for sound systems. i have a qty and price column that i input and then a column which gives me the overall total. ie qty 2 x unit amount 160.00 = 320.00 i also have a discount column that i use and a column that gives them the final price after discounts. so i will put 22% into my discount column and get 249.60 as my final price for the 2 items after the discount.
what i am wanting to do is "round up or down to the nearest 5.00 mark. so for instance, the final price in this case would be 250.00 is this possible?
View 9 Replies
View Related
May 23, 2009
I have a worksheet (Sheet1) that gets information from other sheets (1)
So in cell D7 I have the function ='1'!K33
K33 is from the Sheet labeled (1) obviously. Now the problem I am having is if the number 20.6, it automatically rounds up to 21. I need to number to round down to 20 regardless if its 20.1, or 20.9.
Since I already have a formula in D7, how do I still get the information from the other sheet but have the number round down for me. Is it possible?
View 11 Replies
View Related
Jan 7, 2010
Am currently calculating the total number of cartons used for different items with this formula:
=SUM(F99/352)+(H99/135)+(J99/110)+(L99/60)+(N99/28)
The divisors are the number of items in a carton.
Would like to round up each of the 5 subtotals in the above formula to the next full number, so that the total will be the sum of full numbers.
What would be the correct formula?
View 4 Replies
View Related
Apr 21, 2008
Cell "CostPerEvent" has the value .298896, and when I run the following statement I expect that A2 will contain the same value .298896, yet it is entered into the cell as .3
Cells(1, 2).Value = Range("CostPerEvent").Value
Is there some kind of implicit rounding going on? I rewrote the code to:
Cells(1, 2).Value = CDbl(Range("CostPerEvent").Value)
But I am curious whether this behavior is documented...
View 9 Replies
View Related
Jan 16, 2007
I have written a macro which includes a number of calculations. This has resulted in a value with a number of decimal places. I want this value rounded to 1 decimal place and this value written to a cell. How can i round this value correctly.
View 4 Replies
View Related
Jul 16, 2007
How can I make excel display 321.165 as 321.16 when I have the cell format set to number and 2 decimal places instead of rounding it up to 321.17?
View 11 Replies
View Related
Jun 24, 2008
custom number format.
The numbers in the worksheet read like this: 114'31.5
The number behind the hyphen are not to exceed 32, after 32 they become a full point (115'00.0).
View 12 Replies
View Related
Jan 9, 2009
I have been trying for ages to get a formula where any number prefixing .5 automatically rounds up and not down as the excel standard does.
16.4999999 does show with no decimal places as 16.
16.5 does show with no decimal place as 16. But I want it to round up to 17.
and obviously 16.50000001 does indeed round to 17 anyway.
Its purley numbers that have .5 I need to go up and not down.
View 5 Replies
View Related
May 22, 2009
In one cell i have £92.00 to 2 decimal places. If i increase that to 4 decimal places it is £91.9998. I need this £92.00 to show as £91.99 (only as 2 decimal places not 4)but when i go back to 2 decimal places it shows as £92.00 again.
View 2 Replies
View Related
Nov 11, 2009
looking for for some help on a fairly simple problem: i've attached a worksheet, and in column B (Due to Supply Chain) i'd like to insert a formula that will subtract 21 days from the date in column Z (Pub Date), and then round that date to the nearest wednesday. is this possible?
fyi: the dates in column Z are in a yyyy-mm-dd format; they don't have to remain that way.
View 6 Replies
View Related
Oct 25, 2009
I have a section of code that build a single sheet workbook that incudes names and phone numbers to upload in to a web site.
The numbers are in internation format eg 4412345678987
The code works fine but when it saves as a CSV Delimited it round the numbers up so they end up 4412346000000.
View 9 Replies
View Related
Apr 25, 2007
I need a formula showing that if a number is less than say 1.25 then it rounds down to 1.0 and if its between 1.25 and 1.75 then it rounds to 1.5 and then if greater than 1.75 then it rounds up to 2. I need it to work for all numbers not just 1.
View 10 Replies
View Related
Nov 21, 2008
I am trying to write a formula that will round numbers to $.05, $1, $5, and $10. The formula needs to be written in a way that If c75 <100 round to $.05, if c75 is greater than $101 but less than $500, round to $1, if c75 is greater than $501 but less than $1,000, round to $5, and if c75 is greater than $1,001, round to $10.
=IF(C75<100,ROUND(C75*20,0)/20)
but i can't quite figure out how to get the rest of it to work.
View 4 Replies
View Related
