| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A non roundup decimal place cell format
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
On Mon, 28 Jan 2008 04:52:01 -0800, Nakal
wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
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" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
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 "Bernie Deitrick" wrote: 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" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
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 On Jan 28, 5:58*pm, Nakal wrote: 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 "Bernie Deitrick" wrote: 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" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
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 "Nakal" wrote in message ... 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 "Bernie Deitrick" wrote: 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" wrote in message ... 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
A non roundup decimal place cell format
Or you may prefer =ROUNDDOWN(A4,1)
Note that Pete's formula rounds in the opposite direction to mine for negative numbers. -- David Biddulph "Pete_UK" wrote in message ... 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 On Jan 28, 5:58 pm, Nakal wrote: 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 "Bernie Deitrick" wrote: 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" wrote in message ... 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.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
| How do i place a roundup functoin at the end of a formula? | Excel Worksheet Functions | |||
| Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
| Highest & lowest place value / decimal places of cell value | Excel Worksheet Functions | |||
| Roundup Decimal | Excel Discussion (Misc queries) | |||
Linear Mode
