I'm copying a column range containing numbers with varying decimals from one worksheet to another. The new worksheet and column are set to have no more than two decimals places in the cells through the formatting options.
However, numbers stay showing their original amount of decimal places, so I tried hard-coding the format with:
I am trying to convert Degrees Minutes & Decimals of a minute (12° 34.567') to Degrees and decimals of a degree (12.57611°). I have the formula to convert latitude, that is two digits, but it doesn't want to work with longitudes, three digits. (see attachment). This is the formula that I am using:
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)
I am creating a graph with a wide range of values (0.06 - 300). The smaller values are barely visible on my column graph. I have tried all of the tricks I know. Any way to get the smaller values to show up instead of just hovering towards the bottom of the graph?
I have some problem when I would like to sum only positive decimals on userform, I would like to separate the numbers with comma (",") and sum them, the format should be "0,0" and only numbers and "," can be given in textboxes. How should I do this?
i have columns of prices in 2 decimal format that I'd like to manipulate, some prices are whole dollars(no cents & no decimals ) and most are dollars and cents in decimal format-- but to do it successfully, i need the decimal removed. I looked at the "format" function but it doesn't seem to allow for that.
is there any way I can have these prices converted to "cent" format( ie removing the decimal, where applicable), showing them not as "dollars & fractions" but as "cents" i'm sure that would solve the problem for me last time I did it by hand and vowed there had to be an easier way!
I am trying to put together a code so that when a user enteres more then two decimal points in the cell value they should get a warning regardless if they formatted the cell to show 2 decimals or not...
Simply the problem is on the sheet I have a lot of people format the cell to show two decimals but when you copy and paste the cell values to other sheets the decimals are still there which causes problems.
I am intermediete in VBA so if someone can shoot me some example codes I think I can make it work on my sheet.
I am trying to combine to cells containing percentages, however when I do the percentages turn into decimals. I have tried using the text funtion, but that just returns the full number 1, no decimals or percentages.
Let's say I have a set of values (A1:A10) where each contains a number, with a varied amount of decimal places (some may have 0, some may have 10)...how can I make a formula in cell B1 that averages A1:A10, ignoring the decimal places (rounding to the nearest whole number)
If I did the Average (=AVG(A1:A10)), I would get 94.2498. But I don't want this, I want the formula to take into account the numbers rounded to the nearest whole number, meaning, I want to take the average of..
93 94 92 95 97 98 99 100 89 86
Which would give me a value of 94.3.. In this example, there isn't much of a difference, but I was simplifying the numbers for time sake
I want round off number to be with the decimals (usually decimals r discarded for round off feature). I ll be able to explain better with an example.
10.36+10.36= 20.72. If i round it up, it vl be 21. what i am tryin is to get 21.00 after round off.
I prepare my bills in excel and after total of all products, if i get a decimal number, I would like it be rounded off to whole number with decimal and 00 at the end (eg. 21.00 instead of 21.27 or 22.00 instead of 21.77). I tried to change decimals but when I select 2 decimal places, it gives me the actual fig(eg. 21.27 instead of 21.00)
I am already using =sum for the total so cannot use another formula (as far as i know, 1 cell can have only 1 formula. Dunno if multiple can be used in a single cell).
I have a weird problem with Excel. It recognises all numbers as numbers but excluding the number 1. It is only recognised as text as well as a decimals, for example 3.4. So every time I try and add these values up it completely ignores 1 and decimals.
I'm converting metric to British values and have no problem with fixing the decimals when displaying the number. However, I'm wanting to CONCATENATE the unit of measure to the conversion and end up with 12 places beyond the decimal. Entered formula is: =CONCATENATE(CONVERT(T5,"cm","in")," in") Results in 159.055118110236 in
But = CONVERT(T5,"cm","in") Results in 159.1 which is what I want.
I have created a worksheet to figure out (in this case) how many sets of Buckyballs i need in order to get both a square number and a cube number. There are 216 balls per set. My worksheet so far has:
Sets Number of balls Square Cube (Number of sets) (Sets*216) (Number of balls ^0.5) (Number of balls ^(1/3))
The problem is that I am looking for the number of sets required to produce an integer in both Square and Cube columns. I have figured out that I can circle all values that are not integers. I am looking for a way to delete all rows with a decimal value or at least all cells with a decimal value and I can delete the rows from there.
I'm going through financial statements and I would like to be able to run full blown analysis on them. Most public financial reports are written so that a lot of zero's do not take up the useful space. As such (and there is a ton of this solution out there I've found), many people want to take $1,500,000,000 and turn it into 1.5M
I however, do not. I want to convert it backwards. So when something says 5.19 I would like to format it in such a way that it will read (and I can run calculations against it) as 5,190,000,000 (there are no alpha characters, there are instances where there will be negative numbers). Then I could copy this to the 17 different financial statements I've already got rock'n rolling in Excel right now.
When I set my cell for Numbers it wants a decimal place. If I set it to 2 decimals then 100.1 (One hundred point one) would look like 100.10 (and print 100.10). If I use 100.10 (One hundred point ten) it looks and prints 100.10. If I set the decimal to one then the problem is reveresed.
Setting to General looks like 100.10 (One hundred point ten) but when using VBa to match numbers it is matched to 100.1 not 100.10.
Now I have it set to Text, but I see when I replace (find & replace) values like find 100 replace 99 the 100.10 becomes 99.1.
Incedently I have a macro that compares (and copies cell values) numbers from one sheet to another and occasionally it skips a number, always a decimal value like 100.10 or 100.1. Or any thing x.10, x.1, x.3, x.19 any combination of x.x or x.xx.
Is there a way to format so decimal numbers are decimal numbers and trailing zeros arent omitted? If the value is one decimal I want one decimal, if its two decimals I want two decimals.
I am trying to round up the total sum of time to the quarter hour. I enter time in fields as follows:
WORKSHEET SETUP: Column B: Time I begin working Column C: Time I end working Column D: Total time C minus B Cell format for columns B and C: H:MM AM/PM Cell format for calculated field in column D: H:MM Calculation, column D to show total time on a daily basis: =IF(C6>0,C6-B6,"")
However, I want to calculate total time per month in decimals, rounded up to the nearest quarter hour.
I worked 5 minutes (I do not want to reflect 15 minutes for five minutes of work). When I total time for the entire month (total daily time in column D), I would like that total to reflect total Hours:Minutes rounded up the quarter hour - but in decimals!
Assuming this is possible, how should I format cells, and what would the formula be for the total of hours per month?
How can I show a decimal as time. For example an item takes 13 minutes to make and I want to make 50, thus using 6.5 hours but really, the 0.5 hours is 30 minutes therefore, I would like to show 6:30 but Excel gets to 23:59 and goes back to 0:00.
I have multiple strings in a selection set. My goal is to replace the decimal point with null for a specific requirement. The requirement is this: "#.## "(Meaning: NUmeric.NumericNumericSpace)
Example String #1: 2.5 kips <---no change Example String #2: 0.6 %%C <---no change Example String #3: W1.4 <---no change Example String #4: 0.086 SQ IN <---no change Example String #5: 6.2.2 <---no change Example String #6: SPC1.20 <---This one should be changed to: SPC120 Example String #7: 1.2000 <---no change
Now the real complicated string(multiple decimal points found in the same string.) Example String # 8: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC1.20 for series 1.2000. #8 should changed to: 2.5 kips with 0.6 %%C using W1.4 mesh with 0.086 SQ IN. See 6.2.2 on SPC120 for series 1.2000. <---Note:SPC120 change. SPC1.20 is the ony part of the string that meets "#.## "(Meaning: NUmeric.NumericNumericSpace)