Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
I did ask this question before, but did not get an answer.
How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
In article , ?B?VGlnZXJ4eHg=?= wrote:
I did ask this question before, but did not get an answer. How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! Well, the question here is, why would you want to ? Why not just put the right number in and display it as it is ? Personally I have enough problems with the 'percentage' format. Put in one number and it looks like another. :) Your question looks a bit like ... ... how can I display "Apple" as "Pear" ? Any hints as to why you want to do this would be welcome ... and you might get some answers. :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
You can format as % which times by a 100, but then you'll have a percentage
sign. Not sure that custom format lets you perform calculations on values. You could do it using VBA, so when a value is entered into a cell its multiplied by 100: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target = Target.Value * 100 End If Application.EnableEvents = True End Sub If you press Alt + F11 it'll open the vb editor, then select the sheet you want it to work on and copy and paste in the above code - you'll have to alter the range to what you want, at the moment it's set to work only on column A. just a thought on how to get round it really... "Tigerxxx" wrote: I did ask this question before, but did not get an answer. How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
Hi Guys,
The reason I needed to do this is because when I display percentage values in a bar chart for the individual bars, the % value is not diaplyed in one line but displayed in 2 rows i.e. -24.3% will be displayed as "-24." above and "3%" below it. This looks very unprofessional. Hence I wanted to format these values to display as just -24.3 which then puts all of them in one line. Hope I was able to express the need. Thank you for your help! "scp3030" wrote: You can format as % which times by a 100, but then you'll have a percentage sign. Not sure that custom format lets you perform calculations on values. You could do it using VBA, so when a value is entered into a cell its multiplied by 100: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target = Target.Value * 100 End If Application.EnableEvents = True End Sub If you press Alt + F11 it'll open the vb editor, then select the sheet you want it to work on and copy and paste in the above code - you'll have to alter the range to what you want, at the moment it's set to work only on column A. just a thought on how to get round it really... "Tigerxxx" wrote: I did ask this question before, but did not get an answer. How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
oh, i see!!
as a simpler solution, why not have the data labels on your bar chart rotated by 90 degrees, so they will then display vertically on one line, might be a bit easier... :) "Tigerxxx" wrote: Hi Guys, The reason I needed to do this is because when I display percentage values in a bar chart for the individual bars, the % value is not diaplyed in one line but displayed in 2 rows i.e. -24.3% will be displayed as "-24." above and "3%" below it. This looks very unprofessional. Hence I wanted to format these values to display as just -24.3 which then puts all of them in one line. Hope I was able to express the need. Thank you for your help! "scp3030" wrote: You can format as % which times by a 100, but then you'll have a percentage sign. Not sure that custom format lets you perform calculations on values. You could do it using VBA, so when a value is entered into a cell its multiplied by 100: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target = Target.Value * 100 End If Application.EnableEvents = True End Sub If you press Alt + F11 it'll open the vb editor, then select the sheet you want it to work on and copy and paste in the above code - you'll have to alter the range to what you want, at the moment it's set to work only on column A. just a thought on how to get round it really... "Tigerxxx" wrote: I did ask this question before, but did not get an answer. How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Custom format numbers
Thank you guys...appreciate your responses.
If you come across other solutions, I would appreciate the feedback. "scp3030" wrote: oh, i see!! as a simpler solution, why not have the data labels on your bar chart rotated by 90 degrees, so they will then display vertically on one line, might be a bit easier... :) "Tigerxxx" wrote: Hi Guys, The reason I needed to do this is because when I display percentage values in a bar chart for the individual bars, the % value is not diaplyed in one line but displayed in 2 rows i.e. -24.3% will be displayed as "-24." above and "3%" below it. This looks very unprofessional. Hence I wanted to format these values to display as just -24.3 which then puts all of them in one line. Hope I was able to express the need. Thank you for your help! "scp3030" wrote: You can format as % which times by a 100, but then you'll have a percentage sign. Not sure that custom format lets you perform calculations on values. You could do it using VBA, so when a value is entered into a cell its multiplied by 100: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = True If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Target = Target.Value * 100 End If Application.EnableEvents = True End Sub If you press Alt + F11 it'll open the vb editor, then select the sheet you want it to work on and copy and paste in the above code - you'll have to alter the range to what you want, at the moment it's set to work only on column A. just a thought on how to get round it really... "Tigerxxx" wrote: I did ask this question before, but did not get an answer. How can I display 0.01 as 1 0r 10 or 100 i.e. how can I custom format numbers to display them as higher numbers i.e. shift decimal to the right? Would appreciate a solution a lot! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Format Numbers to $$ | Excel Discussion (Misc queries) | |||
custom format cell with text and numbers | Excel Discussion (Misc queries) | |||
How do I keep numbers from rounding in a custom format? | New Users to Excel | |||
Custom Format for Numbers | Excel Discussion (Misc queries) | |||
How to format text and numbers as custom | Excel Discussion (Misc queries) |