Change Default Decimal Points For Numbers

Feb 14, 2008

I've got a wierd simple problem in Excel 2003. I have a laptop and a desktop machine, and I'm an accountant who uses the fixed decimal feature as a default, with it set to 2 decimal places.

On the desktop machine, if I input "23." into a cell and then hit enter, the value left in the cell is "23.00" However, on the laptop, when I do the same thing, I get "0.23" In essence, it ignores the fact that I entered a decimal point.

If I enter "23.0" in a sheet on the laptop, it puts "23.00" in the cell.

I thought maybe it was some sort of hardware thing, like the KB was messed up, but then I used remote desktop to try to see what would happen if I enter numbers into the other computer using the laptop, and into the laptop using the desktop. The laptop when connected to the desktop performed normally, just like the desktop machine. The desktop, when connected remotely to the laptop behved like the laptop machine. In other words, I believe this test takes Hardware out of the picture.

Which leaves some strange obsure setting in excel someplace tht is causing this behavior, and I can't seem to find it.

Does anyone here know why these two installs treat the data entry so differently? It's driving me nuts.

View 9 Replies


ADVERTISEMENT

Changing Default Number Of Decimal Points?

Oct 24, 2012

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

View 5 Replies View Related

Reducing Decimal Points Without Rounding Up The Numbers

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

Phantom Decimal Points

Sep 29, 2009

I've tried to look for a solution on the forum, but nothing seems to come up. I've attached a file to help show what I'm trying to resolve.

Column A of the file shows an amount, when summed, give a total of -3.5725E-09.

Each of the figures in column A only has 2 decimal points and if I manually total up the numbers on a calculator it give me zero.

Does anyone know how I can get rid of the -3.5725E-09 without another formula? I need the balance to be zero.

View 10 Replies View Related

Number Won't Display With Decimal Points?

Jul 3, 2013

I have a few cells that contain decimals that do not appear as decimals. If the number is 50.1, it only shows up as 50 in the cell. I've tried formatting it to a number with 2 decimal places and to scientific and when I do that the number displays as ##. When I do general formatting it only appears as 50.

I used a formula to pull the number into a different cell, however, and in that cell it shows up as 50.1.

Is there anything I can do to make it show up as 50.1 in the original cell?

View 2 Replies View Related

Macro -Delete Decimal Points & Add 0's

Nov 18, 2008

I am in need of a macro which would be capable of doing the following;

Replacing a sequence of number's & decimals such as 58.6073.1 with 58607310000
Essentially, the code will just need to remove the two decimal points & to suffix those 4 zeros onto the end of the number sequence.

View 3 Replies View Related

Prices Not Rounded To 2 Decimal Points

Mar 5, 2009

I beleive it is because the formulas are not rounding the figures to the nearest 2 decimal points. so some costs are 1 or 2 pence out. OK on a small qty not so good on a large qty.

I dont know how to do this as the roundup function only rounds up i need it to round to the neareast 2 decimal points. Also dont know how to integrate it into existing formulas and macros

View 5 Replies View Related

Removing Decimal Points From A Number

Jul 17, 2007

Is there some kind of function that will remove decimal points from a number? What I'm looking for is:

1231.5 = 12315
123.15 = 12315
12.315 = 12315
1.2315 = 12315

View 6 Replies View Related

Stopping Zeroes From Being Dropped After Decimal Points?

Feb 10, 2014

I've read various "solutions" to my request but it's Greek to me. Simply stated I occasionally have to create worksheets that involve dollars and cents. My question is how can I get Excel to stop dropping the zero if its the last digit to the right of the decimal point? $42.30 becomes $42.3, etc.

I've looked through the preferences/options and can find nothing to rectify this. Any way to do this so that keeping the zero becomes the default for any tables, lists, etc that I create in the future?

View 4 Replies View Related

Combining Simple Formula With Text Concatenation Produces Too Many Decimal Points

Feb 18, 2014

This is my formula with concatenation: =K3/K1*100 & "c" & " Each"

This is the result of the values in K3 divided by K1: 6.41666666666667c Each

I would like to limit the decimal places to two: 6.41c Each.

I have tried to format the cell and none of the formatting options have any effect.

If possible I would like to use the rounding function but I do not know how to combine the formula + text + rounding.

View 9 Replies View Related

Spreadsheet Default Inserted A Decimal At 2 Places

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

Converting Decimal Numbers To Text With Dot Numbers

May 27, 2006

we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers:

Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
. Range("IV65536") = vbNullString
.Range("IV65536").Copy
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End With
Next wSheet
End Sub

Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

View 3 Replies View Related

How To Add Numbers - Input Points

Jun 25, 2013

How to add numbers such as if a person has 15 points how do I input two points and have them added up to 17 points does it require multiple sheets or is there a command to do this seemingly simple operation.

View 4 Replies View Related

Change Border Widths Via Points

Mar 6, 2007

if it is possible to change the border thickness weight via points? Eg., "1/4 pt".

I find that the xlthin border prints too thick. The only way I've been able to reduce it to appearing thin when it prints is by choosing the second option under "None" in the Line Style option of the Format Cells dialog box.

While that approach prints a thin border, it is actually a dotted line and looks a bit odd on screen.

View 3 Replies View Related

Banding Numbers Associated Trigger Points?

Mar 4, 2014

We have a customer rebate in place with various levels of refund based on the quantity purchased during the year. I have used a sumproduct formula to calculate this before.

The customer used to have the following set up -

0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £3.00 per unit rebate.

So if they bought 2501 units they would get a rebate of (1000*1)+(1000*2)+(501*3). However the customer has trigger points so rather than the above it is now -

0-999 - £1.00 per unit rebate.
1000-1999 - £2.00 per unit rebate.
2000-2999 - £4.00 per unit rebate if 2500 bought.

So now it would look like this - (1000*1)+(1000*2)+(501*4). However if they only bought 2499 units it would be (1000*1)+(1000*2)+(499*2).

View 3 Replies View Related

Charting 2 Data Series With Different Numbers Of Points

Jun 12, 2014

Have Series 1 (to be a line chart) with 20 data points X values are 0 to 68. Have Series 2 (Bar chart) with 68 data points, again X values 0 to 68

Cant get series 1 to display beyond X value of 20

View 3 Replies View Related

List Only Whole Numbers From Decimal Numbers

May 23, 2009

From a list of numbers I would like to delete values that have cents so only those transactions with a .00 amount are displayed

For example

34.95
21.88
21.00
56.00
45.77

Only those valaues ending in .00 will list. I tried filtering but I think there most be a function(s) string that might work or at least filter out the values with cents

View 4 Replies View Related

How To Change Default Row Height

Aug 18, 2006

inability to set a default row height and still allow the occasional row that requires more space (i.e., one of it's cells has multiple lines of wrapped text) to AutoFit if necessary.

Say you have the data shown in NormalSettings.png in an Excel file (see attachments) and you want to have a little more room between the shorter rows, but not have them all set as big as the expanded rows. One way to do this in bulk is to select the entire spreadsheet and set a fixed row height, but this chops off any cells that require more space than your default height (see FixedRowHeight20.png). The only way to fix this is to manually scan through your sheet and individually select all rows with cells that need more room than your preferred row height and AutoFit them (good luck if your spreadsheet is any size...) . If you try selecting the entire sheet and choose Format-Row-Autofit, you're back to the crowded display shown in 'NormalSettings.png'.

My workaround to this annoying problem is this: choose a column that you're not using (I just select the very last column in the sheet by holding down CTRL and pressing the right arrow until the screen stops moving) and highlight the entire column by selecting the column header. Then increase the font size for that column...voila, even your blank rows will now AutoFit to the new font size rather than the font size you are using for your data...effectively increasing your default row height without sacrificing AutoFit capabilities. I find that using 16 as a dummy font size makes my data (which is font size 10, Arial) look nicely spaced out, but experiment and try stuff until you find what you like!

View 1 Replies View Related

Change The Default Settings

Dec 16, 2008

how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?

View 4 Replies View Related

Change Default Printer

May 17, 2009

I have got a buttion that changes various layout and borders for the page then shows a printpreview, after printprewiew closes.

Then a msgbox that askes if you would like print. Is there a way to change the defult printer between local printer and adobe distiller(.pdf) as a dropdown option box. Or Yes for Local printer and NO for adobe distiller(.pdf) using vbYesNoCancle.

View 9 Replies View Related

How To Change The Default Module Name

Jan 14, 2007

When I open Visual Basic Editor and insert a module,the default name

is "Module1",How can I change the default as "Mymod1" or others ,and if

I insert another module later,it can be named as "Mymod2" by default?

I have searched the registry and found nothing about the item.

View 9 Replies View Related

Change Default Color With VBA

Jan 18, 2008

I am setting the fill color of a cell by setting the .Interior.Color property and using the RGB() VBA function. My VBA code saves the spreadsheet in Excel2003 format at the end of the creation process. This has worked great and my spreadsheets looked correct when reopened in Excel 2007. When these same spreadsheet files are opened in Excel 2003 the colors are all very different. Does Excel 2003 handle colors differently than Excel 2007? Is there a preferred way to specify colors that will work on both platforms?

I have attached a specific example. The color is #B4CF27 or RGB(180, 207, 39). When I open this sheet in Excel 2007 the color is a shade of green (the desired color). When I open it on Excel 2003 I get yellow. I tried swapping the red and blue values thinking there might be some byte ordering issue and it does not produce the yellow on Excel 2007.

View 2 Replies View Related

Change Default Comment Box Size

Oct 14, 2009

I need to default size to be bigger. Is there a way to do this so I do not need to re-size the box every time?

View 2 Replies View Related

Change Excel Default Row Height?

May 28, 2013

I want to set the default row height as 20.0 (not excel's standard 15.0). When I delete the contents in a cell that has Wrap Text property enabled, the row always reverts back to default height of 15.0 and this is too small. Is there a way to chnage the default?

View 6 Replies View Related

Confirm Save As - Change Default?

Jan 5, 2014

Whenever updating an Excel file and saving it as a new file, a "Confirm Save As" window appears. The default is "NO" (No is Highlighted). Conversely, in MS Word, the default is "Yes" (and, YES is highlighted). I would like to change the default from NO to YES in Excel.

View 1 Replies View Related

Change Default Cell Color?

Mar 10, 2014

I would like to change the default color of any excel sheet I use to grey from the standard white and I am struggling to find out how this is achieved.

In other words, I am trying to have all cells in the sheets I work with appear grey on my screen by default. A coworker of mine used to work in banking and she has grey cells but does not know how she changed them.

View 1 Replies View Related

Change GetOpenFilename Default Location

Oct 29, 2008

When I use the GetOpenFilename() method, it seems to default to the particular users My Documents. Is there a way to force it to default elsewhere like a share drive, or the users desktop?

In the same line of thought, if I have a file named TestFile.xls. and I want to try to open it from the users desktop if they have it, how can I do this, since their desktop location is different than mine?

View 12 Replies View Related

Change Default Color Palette

Aug 15, 2008

I'm trying to change the default colour palette for "fill colour" and "font colour". The way I've been doing it before, is to have a spreadsheet with the colour palette I want in my XLSTART folder, and then when I open a new document, going to tools->options->color->copy colors from, and selecting that spreadsheet.

However, this is a pain, and I was wondering if I can just set the colour palette to have my colour scheme already there, whenever I create a new spreadsheet.

View 3 Replies View Related

Change Default Date Format

Oct 3, 2006

i am trying to change the date format for the B column.

if you look the format for this column is dd/mm/yyyy, BUT, it is not. the real format should be mm/dd/yyyy.

ex. 08/01/2006 (mm/dd/yyyy) but excel thinks it is dd/mm/yyyy

View 9 Replies View Related

Change Default Settings For New Workbooks

Jan 2, 2007

How can I change the default settings I get with a new Excel document?

What I want is that when I create a new Excel document, all cells should have Comic Sans MS font, Bold & size 10, with a particular horizontal and vertical alignment etc.

I am not able to do these changes using settings in Option menu.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved