# Add X Decimal Places To Numbers But Not Zeros

May 24, 2008
I have a column of numbers with one decimal point and some blank entries too. I need to convert all the numbers (including the zeros, but not the blanks) to two decimal points. Any idea on how this can be done?

Examples: 0.1 needs to become 0.10, 0 needs to stay 0, a Blank cell needs to remain Blank.

View 9 Replies
ADVERTISEMENT
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
Jun 4, 2008

I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).

What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.

I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.

I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.

If any of this is not quite clear, please ask and I can further expand on what I am doing.

Here is the code I have right now that is not working correctly (and I know why it's not): ....

View 9 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
Apr 15, 2008

I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. how to do this automatically?

View 4 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
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
Aug 5, 2013

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

View 1 Replies
View Related
Jun 22, 2009

I am converting values from SAE to Metric and wish to retain the decimal places of the SAE value before converting.

My code below converts nicely but doesnt maintain decimal places.

View 8 Replies
View Related
Jan 16, 2010

What do I need to add to this code to set the decimal places to zero?

View 2 Replies
View Related
Aug 2, 2006

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.

View 4 Replies
View Related
Sep 4, 2006

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.

View 7 Replies
View Related
Oct 16, 2006

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]

View 2 Replies
View Related
Nov 30, 2006

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?

View 9 Replies
View Related
Jun 6, 2007

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.

View 3 Replies
View Related
Jan 21, 2005

I want to show (in a cell) how much decimals there are after a comma.

View 9 Replies
View Related
Jun 5, 2014

Is there a way to format a number into 2 decimal places and when you select the cell you wont see the true value?

For example:

316.2696

I still see the 316.2696 when I click the cell. Instead of 316.27 only.

View 4 Replies
View Related
Mar 13, 2014

I'm on Excel 2010 and I need to round up a complete price list to a 0.90 decimal.

Here is the formula that I have in my cell : =((J5-(J5*N$2))*M5) I need to round the result to XXX.90

View 4 Replies
View Related
Dec 11, 2008

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.

View 2 Replies
View Related
Jan 15, 2009

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:

View 4 Replies
View Related
Nov 23, 2009

I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered.

In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4.

Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?

View 3 Replies
View Related
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
Apr 8, 2013

Using Excel 2010

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.

View 4 Replies
View Related
Feb 24, 2010

if I change the formatting of a cell to 2 decimal places, it appears as two decimal places (as it should) for example $88.88888 will show as $88.88

However, when I use this data in another application that displays this data it will display as 88.88888 still. I need to actually take the value and truncate it to 88.88 eg 8.8888888 will become 8.88

I have been using trunc by hand and wanted to try and find out if there is a way that I could write some sort of macro to do this for me each time.

View 14 Replies
View Related
Oct 23, 2008

How do I stop a user from entering more than 2 decimal places?

If I format the cell to "0.00" I can still type in 1234.1234 and it will display 1234.12.

If I select that cell, the value in the formula bar is still 1234.1234.

View 9 Replies
View Related
Dec 10, 2008

I have a problem with the currency format with 4 decimal places.

Sub go()

Range("C5") = Range("C5") + Range("C4")

End Sub

as you can see it adds the conecnt of "R4" to what is in "R5"

If works fine when the calls are formatted as number with 4 decimal places.

example if R4 is 0.1111 and R5 is 0.2222 after running the macro R5 becomes 0.3333

But if the cells are formatted as currency and 4 decimal places the then excel returns $0.3300

View 9 Replies
View Related
Jul 27, 2007

Is there a function in VBA that lets me increment or decrement the number of decimals of a particular cell? let's say i got a cell value of 7.123456 but excel only displays 7 on the cell. What if i want excel to display only 7.123? How can I specify the number of decimals i want on a particular cell (in this case 3)

View 2 Replies
View Related
May 10, 2014

AVERAGEIFS(X$2:X$1000,$AT$2:$AT$1000,1)& TEXT(BE2/BD2," 0%")

The formula is working , but the result is : 3.50239234449761 58% Formatting the cell did not work. Can these decimals be slashed?

View 5 Replies
View Related
Mar 20, 2008

I have to change a time sheet that was previously in HH:mm to display in decimals, but to the nearest 1/4 hour ( 0.25 (=15 mins), 0.50 (= 30 mins), 0.75 (=45 mins), and 1.00 (1 hour))

This is not a formula question, but a format/display question as the cells have to be typed in by different people who up until now have beeb used to typing in the HH:mm format.

This means;

8:15hrs would become 8.25

8:30hrs would become 8.5

8:45hrs would become 8.75

The reason behind the change is a head office wallah that needs to have time in decimals and not true minutes.

Is there a way to;

prevent user from entering any decimal other than .00; .25;.50;.75

or change formatting to turn red if any other than above is entered.

Alternatively, is there a way of me (the collator for 50 people) being able to take data from their sheets (which I have to do monthly) which could be a formula...

I have tried to get head office to change, but they say that they collate data for 13 other sites and all the others are OK (thats cos all the others receive their data on PAPER and one person tabluates them)...

View 9 Replies
View Related
Dec 1, 2012

Is it possible to add a data validation to a cell which restricts the user from inputting more than 2 decimal places?

View 6 Replies
View Related
Jan 24, 2014

I solved my problem by going to the multi-coloured MS Office Button, top left corner of the screen, then selected Excel Options there, clicked on Advanced, & deselected the 2nd from top "AUTOMATICALLY INSERT A DECIMAL POINT" ... AT 2 PLACES.

View 1 Replies
View Related
Mar 24, 2009

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.

View 3 Replies
View Related
Dec 27, 2011

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

View 9 Replies
View Related
Nov 21, 2012

I need to have textBox2. formatted to a number with 3 decimal places.

The code below is executed from the command button.

Code:

Private Sub CommandButton1_Click()

If IsNumeric(Me.TextBox1.Value) Then

Me.TextBox2.Value = Me.TextBox1.Value / 25.4

End If

End Sub

I found the code below but I cannot seem to figure it out

Code:

Private Sub TextBox2_Change()

TextBox2.Text = Format(Number, "0.000")

End Sub

View 2 Replies
View Related
May 12, 2013

I have a combo box where the selected abbreviation will affect the number of decimal places in another cell where figures are manually entered. The decimal places will range from 0 to 5.

The combobox contains abbreviations of product codes i.e TM, FGGT, etc up to 40+ & the range of value can vary from 4 digits to possibly 10.

So what I am looking for is if I select TM another four columns require digits to be manually input with 2 decimal places, however, if I select FGGT the same columns in a different row will need to be 4 decimal places as per the product code selection from the combobox.

e.g TM = 2 decimals

FGGT = 4 decimals,

View 1 Replies
View Related
Jul 2, 2013

I have a list with a variety of numbers:

a) some with large units (never more than 10 units long) and possibly also decimals,

b) others with minimal units but long decimal characters,

c) and others containing both.

I need a formula that can be applied to each individual number, that removes a variable amount of decimal places, resulting in a maximum character length of 14 (i.e. sometimes removing a few decimal places, other times removing a substantial amount of decimal places). Removed decimals are displayed in red in the following example

a) 1234567890.123456789

b) 1.2345678901234567

c) 123456.789012345678901234567890

View 6 Replies
View Related
Jul 12, 2014

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

View 2 Replies
View Related
Oct 12, 2007

I have following for change event in text boxes to only allow numerics e.g.

Private Sub txtGBP10_Change()

If (Not IsNumeric(txtGBP10.Value) And (txtGBP10.Value <> "")) Then

txtGBP10.Value = Left(txtGBP10.Value, Len(txtGBP10.Value) - 1)

End If

End Sub

Private Sub txtShare10_Change()

If (Not IsNumeric(txtShare10.Value) And (txtShare10.Value <> "")) Then

txtShare10.Value = Left(txtShare10.Value, Len(txtShare10.Value) - 1)

End If

End Sub

Can I add some code so that the user can only add numeric entries to 2 DP (txtGBP) or 3DP(txtShare)

View 5 Replies
View Related
May 17, 2008

I would like to do the following: * Format numeric cells (cells with a percent sign) to zero decimal places. I have a set of macros that I am combining to clean up a data set and these are two of many (removing blank lines, duplicates, blue/bold, etc.)

View 2 Replies
View Related
May 14, 2014

How do you restrict a user to only be able to enter up to 2 decimal places in a cell without it automatically rounding for them?

View 6 Replies
View Related