Stop Excel rounding decimal numbers up to next integer

[Bill Hamilton]

I have a worksheet that requires the user to insert a number with one decimal (eg 10.1) but the number has to be displayed in that cell only as the integer part of the number (eg 10). The number is copied by formula to another place in the sheet and that cell has to display the decimal number (eg 10.1). The numbers will range from 0.0 to 100.9.

Setting the second cell as a 1-DP cell is not a problem but formatting the entry cell to show just the integer part is. No matter how I format the cell, entering anything with a decimal of 5 or more will round the number up to the next integer.

I know I could get the user to input the decimal number to the cell that shows the decimals and refer to it with the INT function in the integer cell, but there are 12 of these numbers plus other data to enter into the input area with the decimal numbers appearing in a summary area and I don't want the users to get confused about where to put things.

I've researched this a bit and found many 'solutions' but none has worked for me or even been applicable to this situation. I obviously can't use the INT or TRUNC functions in the cell the user is entering data to and formatting it as ##0 or for it to have 0 decimal places still round the number up.

Any ideas?

(Excel 365 on Windows 11)

 

[Scott Huish]

It could be done with VBA assuming there's not a huge number of cells you're doing this to by formatting the cells as the integer value itself.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set c = Intersect(Target, Range("A1:A50"))
If c Is Nothing Then Exit Sub
For Each d In c
    If IsNumeric(c) Then c.NumberFormat = Chr(34) & Int(d) & Chr(34)
Next
End Sub

 

[Snakehips]

@Bill Hamilton I was having similar thoughts re vba.
Code assumes entry decimal in column B with decimal value being retained in E. * edit code to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim InptRng As Range
Dim ColOs As Integer

Set InptRng = Range("B2:B12")     '************Edit to suit
ColOs = 4    ' Offeset from B to  ?  ***** *****Edit to suit

If Target.Cells.Count > 1 Then Exit Sub
'Check if Target is in input range If not exit sub
If Intersect(Target, InptRng) Is Nothing Then Exit Sub

'Otherwise
Application.EnableEvents = False
Target.Offset(0, ColOs) = Round(Target, 1)
Target = Int(Target)
Application.EnableEvents = True

End Sub

 

[rlv01]

@Bill Hamilton, Are you sure this is not an XY problem? Why is it important for the displayed number to be the truncated entered number instead of the rounded entered number? Putting myself in the shoes of your user, if I typed in a value of 10.9 and the cell displayed 10 instead of 11, I would find that confusing.

 

[Bill Hamilton]

Thanks to the two gentlemen who provided a VBA solution. I'm well versed in VBA and it was my fallback to use some code in Worksheet_Change to deal with this. I was hoping for a more straightforward 'native' solution, but it looks like I'll be using Plan B.

I won't say thank you to riv01. I find it a little patronising to be told that I might not know what I actually want and why just because he doesn't understand why. I do know what I'm doing and why.

 

[rlv01]

@Bill Hamilton, sorry that you interpreted my post as patronizing. That was not my intent. I'm glad you found a solution.