Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format
Am trying to custom format a field in a pivot table. I want to display the
decimal point and 2 decimal places, unless the value is an integer. With format '0.??' I achieve what I want with the decimal places. But then all integer values still display the decimal point. How do I remove this? Olly |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom Number Format
I don't think you can do this with just formatting.
But you could use a macro that looks at the value. But since you're writing about a pivottable, it would seem to me that with all the ways you could pivot the data, you'd want to run the macro on demand. If you want... Option Explicit Sub testme() Dim myCell As Range Dim myRng As Range Set myRng = Selection For Each myCell In myRng.Cells With myCell If IsNumeric(.Value) Then If .Value = Int(.Value) Then .NumberFormat = "0_._0_0" Else .NumberFormat = "0.00" End If End If End With Next myCell End Sub Just select the range and run the macro. I was trying to think of way that this could be automatically. But with all the stuff you can do with pivottables, I don't see a good way. Olly wrote: Am trying to custom format a field in a pivot table. I want to display the decimal point and 2 decimal places, unless the value is an integer. With format '0.??' I achieve what I want with the decimal places. But then all integer values still display the decimal point. How do I remove this? Olly -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Number format question | Excel Discussion (Misc queries) | |||
number format | Excel Discussion (Misc queries) | |||
Custom number format - Accounting | Excel Worksheet Functions | |||
Custom number format always defaults last number to 0. | Excel Discussion (Misc queries) | |||
How do I format a cell for a custom part number? | Excel Discussion (Misc queries) |