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!

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. :)

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!

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!

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!

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!