# Format: Using Numerals And Decimals

Jul 10, 2009

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.

## Number Format (4 Decimals Places) In VBA

Jun 23, 2009

I am having an issue with number format in my VBA code. The entire column of column D is format as ###.0000 (4 decimals places). There are certains cells which require zero decimal places. I am using the following bit of code, but it is not working correctly. It is still formatting the cell as 4 decimal places.

## Alter The Number Of Decimals In A Custom Format

Oct 24, 2008

is there any way by which you can alter the number of decimals in a custom format using just custom format process (ie no VBA).

For ex:

12 should appear as 12
12.1 should appear as 12.1
12.26 should appear as 12.3

Note 12 should NOT appear as 12.0

So point being adding the "m" is an irrelevance at this time - the key is to vary the decimal points essentially based on MOD(value,1) = 0

If you can (I'm really thinking you can't) let's extend it such that 12.1 should appear as 12.1 but 12.26 as 12.26

I know you can use [value>x] type rules but I'm guessing you can't use formulae in those rules ?

## Cell Format: Only Numbers (no Date) With Any Amount Of Decimals

Sep 8, 2009

I need a cell to restrict the input:
-Only numbers are allowed.
-No date posible.
-Any amount of decimals (they must all be shown in the cell).

I tried using the data validation and using the IsNumber() to restrict any non numeral input. The problem with this approach is that if the user enters a date; it apprears as a date format (eg: "5.May"). I'm using an european excel, where the decimal separator is a comma instead of a point; so if a user accidentaly types "5.5" instead of "5,5"; the cell will show "5.May".

I also tried the cell format/number/number format. The problem in here is that I dont know how many decimal positions will the input number have; and I need them all to be shown.

## Add A Column With Mixed Numerals

Feb 22, 2006

I want to be able to add a column with mixed numerals. For example: The sum
of A2:A34 with the values in the cells being 1B or 1F or so.

## Convert Degrees Minutes & Decimals Of A Minute (12° 34.567') To Degrees And Decimals Of A Degree (12.57611°)

Oct 29, 2008

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:

=IF(A2="","",(INT((LEFT(A2,3)+MID(A2,4,6)/60)*100000)/100000)&"d")

## Conditional Removal Of Numerals From Cell Value

May 18, 2009

I'm creating some workbooks from PDF files. After converting, some of the
values in column A get placed into column B along w/it's values. Column A should have only a 6 digit number. Column B may have a combination of numbers and letters, but more use of letters.

I'm trying to create a formula for that will remove the 6 digit number at the front of my text values if it is present in column b. If it is not, than the column should remain the same. Is there a way to say remove first six values
from left if values are numbers? The results should out put to sheet 2.

## Date Cell - Enter Spaces Between Numerals

Aug 20, 2013

I am attempting to enter a code for the current date (m/d/yyyy). For appearance purposes only on the form I am using,

I am trying to enter spaces between the numerals. (" "&m&" "," "&d&" "," "&yyyy&" ".

I can only get the serial numbers to come up, no matter how I try to format the cell.

## Deleting Suffix Info For Numerals (large Scale)

Oct 3, 2008

I have a column filled with 5 digit numbers and a suffix. (Ex: 12345-001) I need to delete all of the '-001' from all of the numbers in the column. There are over 11,000 numbers. Is there a formula or function that I can apply to the entire column that will delete the suffix?

## Getting Decimals To Appear On Graph?

Jul 17, 2014

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?

## Ratio With Decimals?

Oct 14, 2009

I am trying to get an exact ratio in excel but am unable to do so. Example:

60,000/58000 = 1.03. The ratio should then be 1.03:1. But excel is showing this is 1:1. Here is my formula

PHP
=TEXT(E55/E56,"0")&":1" . I need it to show it to two decimals.

## Sum Only Positive Decimals

May 23, 2014

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?

## Numbers Have Too Many Decimals

Feb 8, 2009

A spreadsheet created by exporting from QuickBooks as a .IIF file is opened in Excel 2003.

A macro multiplies a cell value and returns 1.77999997138977 rather than 1.78. The 1.78 is required for importing back into Quickbooks.

Part of the code is: ...

## Round Down To Two Decimals

Dec 16, 2009

i am looking for some formula to round off a decimal number

e.g. A1=8.288
if I use the formula ROUND(A1,2) the result is 8.29.

But I am looking for a result 8.288. That means it shoukd not round off rather the after decimal we should have only two numbers.

8.288 should be 8.28
27.4627 should be 27.46

## Removing Decimals

Feb 4, 2007

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!

## Warning When More Then Two Decimals Are Used

Mar 30, 2007

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.

## Decimals To Percentages

Jan 4, 2008

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.

## Rounding Down Decimals

Jul 22, 2008

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:

Sheets("Toolset Scorecard").Columns("G:G").NumberFormat = "0.00"

## Ignore Decimals In A Formula?

Mar 2, 2014

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)

For example, if this was A1:A10

93.11
94
92.12321
95.1
96.7
98.1
99
100.03
88.6677
85.6675

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

## Round Off Number With Decimals At The End?

Jun 21, 2014

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).

## Recognising 1 Or Decimals As Numbers

Dec 10, 2007

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.

Have I modified a setting?

## Fixied Decimals With CONCATENATE

Jun 11, 2009

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.

## Delete Rows With Decimals?

Dec 17, 2012

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.

## Turning Decimals Into Millions

Feb 13, 2014

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.

## Sorting With Multiple Decimals

Jun 11, 2009

I'm looking for a way to sort things with multiple decimals (version number). Is there a way to have something like this:

1.1
1.57
1.1.5
1.2
1.68.23
1.23.1

become:
1.1
1.1.5
1.2
1.23.1
1.57
1.68.23

I could do it all manually, but I have to work with like 400 entries at a time.

## Macros Won't Work With Decimals

May 8, 2009

I've been trying to create an excel macro to calculate shortest route path using floyd algorithm..

Problem is, i can't figure out why this macro won't work with decimal numbers..
I'm pretty sure there's a VERY simple explanation for this..
Probably has something to do with excel's formating

See the attachment and press Run button, it won't produce the correct result, but if if you multiply all the numbers in "input" sheet by 100.

## Range.Value = A() Chopping Off Decimals

Jun 30, 2009

When I use this: ....

## Converting Decimals To Fraction...

Sep 25, 2009

Can anyone help me with a code for converting decimal number to a nearest 1/16 th fraction. For example converting number 2.1875 to 2 3/16 and so on...Also if it is 2.5 it should display 2 1/2...

## Round Time To Decimals?

Nov 9, 2013

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.

Example:

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?

## Fractions To Decimals With And Without Hyphens

Feb 12, 2014

I have fractions and whole numbers in a column as per the following.

E13 = 3/4
E15 = 5
E17 = 1-1/2

I can use the formula =SUBSTITUTE(E17,"-"," ")+0 which gives the correct result of 2.5 and also correct
for E15 with the answer 5.

But I'm stumped how to get it correct for E13 where it is less than whole number.

I can use the formula =IF(E13=0,0,("0 "&E11)+0) but that won't work for the remainder of the cells.