Excel 2007 number without decimals but not automatic rounding?

Question

I seem to have a pretty simple problem with Excel, although I can't find a solution for it other than a function.

Basically, I would like to show the price in two fields, one for dollars and one for a cent.

It works fine for me, but the problem that I am experiencing is that when I say “24990.55” in the dollars box, it will show “24991” in dollars and “55” in the cents box.

Is there a way to force Excel NOT to Auto-Round?

Answer 1

If a

A1 = 24990.55

then

• =INT(A1) will return 24990
• =MOD(A1,1) will return 0.55

You need a function to disable decimals. Int() will do this. Mod() will only show decimal points.

I do not know what behavior you expect without using functions. Just formatting the number will not change its base value. This means that there is no formatting to show only an integer value and ignore decimals without rounding. Excel does not work this way. Formatting without decimal points will always include rounding. To get around this, you need a function to cut decimal numbers.

If you want the cent to be displayed as integers, just multiply the result of Mod() by 100.

Edit: You are talking about functions above, but reading the other answers, I think that you really mean vba , UDF, or some other macro. You can get the right terminology when asking a question.

You really need to clarify what you want to achieve. Unclear.

• where do you want the exit, for example. Do you want to get the result in the same cell where the original number is entered? Where should cents go?
• Do you want the cents to be displayed as 0.55 or 55?
• If you want the values ​​(dollars and cents) to be displayed in the same cell, what does it look like?
• if you need values ​​in two separate cells, indicate which cells are for dollars and which cells are for cents

Just giving generosity to a question without clearly stating your requirements doesn't help much.

Here is another approach based on the following assumptions:

• a decimal value is entered in column A
• value should be changed in column A to show only dollars (integer)
• decimal values ​​will be shown in column C
• decimal places will be shown as integers in column B

This can be achieved using the change event macro:

 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A:A")) Is Nothing Then On Error Resume Next Application.EnableEvents = False Target.Offset(0, 2) = Target - Int(Target) Target.Offset(0, 1) = (Target - Int(Target)) * 100 Target = Int(Target) Application.EnableEvents = True End If End Sub 

Right-click the sheet tab, click View Code, and paste the above code into the code window.

Of course, a much simpler way to achieve exactly the same thing without functions without macros without any VBA can be performed with exactly the same number of keystrokes as entering a number in a cell.

Compare these two sets of keystrokes

2 4 9 9 0 . 5 5

from

2 4 9 9 0 Tab 5 5

A second set of keystrokes will put cents in its cell, showing them as an integer.

I would really appreciate some feedback on the many offers you received in this thread.

Answer 2

You can use the second cell to calculate the value. if your value is in cell A1, the formula will be

 =A1-MOD(A1,1) 

If this is an option, put your data entry on one worksheet (tab) and print the non-rounded output where you need the final answer.

Answer 3

This is impossible to do. However, if you want an odd solution:

• Assuming the value is in A1, enter the formulas in B1 and C1, see screenshot.
• Insert a TextBox . When you drag it to create it above cell A1, hold down the Alt key so that it fits perfectly with the cell.
• Click on the edge of the TextBox and type =C1 in the formula bar.
• Click the "Align Right" button on the ribbon; you can right-click, "Format Form", for other options.
• Format the text box on a white background, not a path (border).
• You can hide column C.

You can copy and paste the shape into A2, it will change the formula to =C2 . Use the Alt key again to resize the shape as needed to fit the cell.

By the way: I do not recommend this approach , but this is an (unusual) answer to your question.

Answer 4

Is it possible to format a cell as general or text? These formats will not be rounded.

Alternatively, you can ROUNDDOWN specify your dollar cell so that it always displays the correct dollar digit.

You can also use a formula like

 =LEFT(A1,FIND(".",A1,1)-1) 

to separate dollars and cents into separate cells. There are several ways to do this. It doesn't have to be complicated if you want to be a little creative.

Whichever route you choose, you need to use 3 cells, not 2. 1 cell for the full amount, 1 cell for dollars and 1 cell for cents. You should have an input cell and 2 cells with formulas, if you are not going to use the code for this.

Answer 5

Just for a giggle, here's another solution.

Assuming you have existing data in columns, and you want to split dollars and cents into two separate columns, you can use the Text to Columns function.

In Excel 2007:

Please note that we are on the "Data" tab. With the column selected, select Text to Columns and you will get a dialog box below. Selected Delimited and click next.

On this screen, select Other and enter a period in the field, for example:

At this point you can click Finish and it will split them into two columns! Woohoo!

If you want to select a destination and formatting for the fields, click Next.

Fancy!

Oh, and here are the results:

Hooray, Excel!

Answer 6

The number of decimal places displayed can be set in the Tools menu in the Options section. Select the number of decimal places in the Fixed Decimal Parameters section. Alternatively, you can use the INT function to retrieve only the integer part of the decimal value.

Answer 7

Unfortunately, I do not see how this is possible without functions.

Required TRUNC function. This will simply reduce the decimal of your number.

Answer 8

As far as I understand, you need to display something like this ...

And for this you need to format this cell in the format that you need to do.

Answer 9

The TRUNC function does not round numbers .... The TRUNC function, abbreviated for the truncate function, is used to remove the decimal part of a number in a set

Answer 10

really you just need to use the left one and find and then add the number of decimal places you want after the search. For example, =LEFT(H4,FIND(".",H4)+2)

Answer 11

It is DEFINITELY! There is a smart workaround to display decimals as whole cents using the number format and without changing the actual value in the cell. Right-click a cell and select Format Cells. On the Number tab, go to Custom.

In the Type field for the user number, copy / paste the following:

 # ¢ 

Then enter this key combination:

 CTRL + J 

And finally:

 % 

Click OK to save the user number format. Then set the cell format to "Wrap Text".

The% symbol multiplies the number by 100, so your 0.37 is displayed as 37. It also adds the percentage symbol to a format that you don't want.

CTRL + J hides the percent character by inserting the line break CHR (10), which moves the percent character to the second line in the cell. Excel will not automatically increase the row height for this cell, so it remains hidden.

The end result is that a cell value of 0.37 is displayed as 37 ¢. For me, this is much better than changing the value of a cell using a formula.