Help with complex custom cell format - NOT VBA

[AmandaCatRuth]

I need to use cell formatting to display numbers rounded to the nearest 10th, as such:

 1022.5468734654 ->  1020 
 1029.5468734654 ->  1030
10222.5468734654 -> 10220

This will need to work for values of 1000-15000.

I know this can be done with mround, but the function is visible when the cell is clicked on. Pasting over values will ruin all of the trailing decimals, which I need to keep. Cell formatting is the only thing I can think of that will display the numbers nicely, but also will show the correct number as the actual value of the cell.

However, I have no idea how to write a custom format that will do this.

Any suggestions?

 

[Dave Patton]

Can you use a helper column and a formula like =ROUND(A2,-1)

 

[AmandaCatRuth]

No. This is on a data report that will be sent to clients.

 

[RoryA]

There is no custom format that will do that, I'm afraid.

 

[AmandaCatRuth]

I ended up doing this:

Sub Custom_Format_Set()
    
Dim rng As Range
Dim RD As String
Set rng = Selection
    For Each c In rng
    
    If c.Value >= 1000 Then
        RD = WorksheetFunction.MRound(c.Value, 10)
        c.NumberFormat = Chr(34) & RD & Chr(46) & Chr(34)
    
    End If
Next
End Sub

Which isn't as efficient as I was hoping, but it will get the job done.