Excel 2007 Number with no decimals but not Auto-Rounded?

Question

I seem to be having a fairly straightforward issue with Excel, although I cannot find a solution to it besides including a function.

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

I have this working fine but the issue that I have is when I put say "24990.55" in the dollars field it will show "24991" in dollars and "55" in the cents field.

Is there any way I can force Excel to NOT Auto-Round?

Answer 1

If

A1 = 24990.55

then

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

You need a function to cut off the decimals. Int() will do that. Mod() will show only the decimal points.

I don't know what behaviour you expect without using functions. Just formatting a number will not change its underlying value. This means that there is no formatting to show only the integer value and disregard the decimals without rounding. Excel does not work that way. Formatting to no decimal points will always include rounding. To work around that, you need a function to cut off the decimals.

If you want the cents to show as whole numbers, just multiply the Mod() result by 100.

Edit: You talk about functions above, but reading other responses, I think what you actually mean is vba routine, a UDF or some other macro. You may want to get your terminology right when asking a question.

You really need to clarify what you want to achieve. It is not clear

• where you want the output, e.g. do you want the result in the same cell where the original number is entered? Where should the cents go, then?
• do you want the cents to be displayed as 0.55 or as 55?
• If you want the values (dollars and cents) to show in the same cell, what should that look like?
• if you want the values in two separate cells, please specify which cells for the dollars and which cells for the cents

Just putting a bounty on the question without clearly specifying your requirements does not help much.

Here is another approach, based on the following assumptions:

• the value with decimals is entered in column A
• the value should be changed in column A to show just the dollars (the integer)
• the value's decimals will be shown in column C
• the decimals will be shown as whole numbers in column B

This can be achieved by the following 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, much easier way to achieve exactly the same thing, without functions, without macros, without any VBA, can be done with exactly the same number of keystrokes as entering the number in a cell.

Compare these two sets of keystrokes

24990.55

with

24990Tab55

The second set of keystrokes will put the cents into their own cell, showing them as a whole number.

I'd really appreciate some feedback to the many suggestions that you have received in this thread.