A non roundup decimal place cell format

[Nakal]

I am looking for an excel cell format which allows a 2 decimal place number
to be
displayed as a single decimal number without the excel roundup which
normally occurs

eg display 13.68 as 13.6 , not as 13.7 and also NOT change the underlying
value.

ie I am looking for a number format to change the display not a math
function eg TRUNC or ROUNDDOWN which change the underlying value.

 

[Ron Rosenfeld]

I am looking for an excel cell format which allows a 2 decimal place number
to be
displayed as a single decimal number without the excel roundup which
normally occurs

eg display 13.68 as 13.6 , not as 13.7 and also NOT change the underlying
value.

ie I am looking for a number format to change the display not a math
function eg TRUNC or ROUNDDOWN which change the underlying value.

I don't believe you will find it.
--ron

 

[Bernie Deitrick]

Nakal,

What you want doesn't exist. As you have discovered, you need to use a column of functions to get
the number to display as you desire.

HTH,
Bernie
MS Excel MVP

 

[Nakal]

Thanks Gents,

but not the answer I wanted to hear. Looks like I am going to have to do
some lateral thinking on this one.

By the way I have found a very temporary fix to my problem. Custom cell
format "13.6" which displays 13.6 when 13.68 occurs in the cell & uses 13.68
for all calculations. But of course it displays 13.6 for any number that
occurs in that cell.

cheers

 

[Pete_UK]

You could use a helper column with a formula like:

=INT(A1*10)/10

and display this to 1 dp, but hide column A from view (although you
use the column A value in all calculations).

Hope this helps.

Pete

 

[Bernie Deitrick]

Nakal,

You could use a macro to apply your specific formatting. Select all the cells and run this macro:

Sub DoFormat()
Dim myC As Range
For Each myC In Selection
myC.NumberFormat = """" & CStr(Application.RoundDown(myC.Value, 1)) & """"
Next myC
End Sub

You could also use the worksheet's change event or calculate event to apply the formatting.

HTH,
Bernie
MS Excel MVP

 

[David Biddulph]

Or you may prefer =ROUNDDOWN(A4,1)
Note that Pete's formula rounds in the opposite direction to mine for
negative numbers.
--
David Biddulph

You could use a helper column with a formula like:

=INT(A1*10)/10

and display this to 1 dp, but hide column A from view (although you
use the column A value in all calculations).

Hope this helps.

Pete