Least number of digits in Y-axis labels

  • Thread starter Thread starter Charley Kyd
  • Start date Start date
C

Charley Kyd

I'm looking for an automatic way to format the value axis with the least
number of digits possible after the decimal. I want the format to switch
from #,##0 to #,##0.0 to #,##0.00, etc. as needed.

That is, I never want the Y axis to display the same number twice, because
that means we need to add more digits to the right of the decimal. And I
never want the Y axis to display "0" in every right-most character after the
decimal, because that means we need to reduce the number of digits
displayed.

Other than using macros, can anyone suggest a way to display the least
number of digits after the decimal?

Thanks.

Charley
 
Charley,

Outside of using VBA, you could try deleting the Y-axis labels and adding a
dummy Y-axis in it’s place. If you were using an XY-Scatter chart, the dummy
Y-Axis would consist of columns 1 and 2 below. Column three is a direct
reference to column 2 (for example C1=B1, C2=B2, etc.). Format column 3 as
TEXT.

Use a chart labeling add-in such as Rob Bovey’s X-Y Labeler or John
Walkenbach’s Chart Tools to set up a reference to the TEXT formatted data
labels. When complete, each Y-axis data label will appear in the same format
as the data in column 3 below.

0 1.09 1.0856
0 2.01 2.01
0 3.01 3.011
0 4.01 4.01056
0 3.02 3.02
0 6.01 6.014

Regards,
John Mansfield
http://www.pdbook.com
 
John,

Unless I'm missing your point, you're mostly talking about data labels. I
want to automatically control the number format of the value axis. About 99%
of the time, the charts will be line charts and column charts. No XY-Scatter
charts.

I don't see any alternative to using a Sub. Not even a UDF could work.

I just hoped that I was missing something.

Charley
 
John,

I found a solution! As the data changes in my spreadsheet, the value axis
automatically changes its number of digits to the right of the decimal, as
needed.

The solution does require a five-line UDF. But that's much more efficient
than launching a macro that loops through every chart in my workbook,
setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next week
and post it at ExcelUser.com.

Thanks for your help.

Charley
 
Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,

Interesting idea. I hadn't thought of that. I would say that 80% of the time
I could get by with three flavors.

Your idea is particularly timely, because my UDF worked during test but
failed in practice. In fact, it keeps crashing Excel.

Thanks, Jon.

Charley Kyd
ExcelUser.com


Jon Peltier said:
Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Charley said:
John,

I found a solution! As the data changes in my spreadsheet, the value axis
automatically changes its number of digits to the right of the decimal,
as needed.

The solution does require a five-line UDF. But that's much more efficient
than launching a macro that loops through every chart in my workbook,
setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next
week and post it at ExcelUser.com.

Thanks for your help.

Charley
Click to expand...
Click to expand...
 
Your idea is particularly timely, because my UDF worked during test
but failed in practice. In fact, it keeps crashing Excel.
Click to expand...

Sure you haven't borrowed one of mine?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Charley said:
Jon,

Interesting idea. I hadn't thought of that. I would say that 80% of the time
I could get by with three flavors.

Your idea is particularly timely, because my UDF worked during test
but failed in practice. In fact, it keeps crashing Excel.

Thanks, Jon.

Charley Kyd
ExcelUser.com


Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Charley said:
John,

I found a solution! As the data changes in my spreadsheet, the value axis
automatically changes its number of digits to the right of the decimal,
as needed.

The solution does require a five-line UDF. But that's much more efficient
than launching a macro that loops through every chart in my workbook,
setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next
week and post it at ExcelUser.com.

Thanks for your help.

Charley


message

Charley,

Outside of using VBA, you could try deleting the Y-axis labels and adding
a
dummy Y-axis in it's place. If you were using an XY-Scatter chart, the
dummy
Y-Axis would consist of columns 1 and 2 below. Column three is a direct
reference to column 2 (for example C1=B1, C2=B2, etc.). Format column 3
as
TEXT.

Use a chart labeling add-in such as Rob Bovey's X-Y Labeler or John
Walkenbach's Chart Tools to set up a reference to the TEXT formatted data
labels. When complete, each Y-axis data label will appear in the same
format
as the data in column 3 below.

0 1.09 1.0856
0 2.01 2.01
0 3.01 3.011
0 4.01 4.01056
0 3.02 3.02
0 6.01 6.014

Regards,
John Mansfield
http://www.pdbook.com


:



I'm looking for an automatic way to format the value axis with the least
number of digits possible after the decimal. I want the format to switch

from #,##0 to #,##0.0 to #,##0.00, etc. as needed.

That is, I never want the Y axis to display the same number twice,
because
that means we need to add more digits to the right of the decimal. And I
never want the Y axis to display "0" in every right-most character after
the
decimal, because that means we need to reduce the number of digits
displayed.

Other than using macros, can anyone suggest a way to display the least
number of digits after the decimal?

Thanks.

Charley
Click to expand...
Click to expand...
Click to expand...
 
Jon,

Okay, I've been playing with this idea. It's a little more complex than it
first appears. If the data ranges from 29.123 to 0, the chart's value-axis
labels need to range from about 35 to 0. No decimals need be shown.

However, if the data ranges from 29.123 to 29.45, the value axis needs to
range from about 28.9 to 29.5.

Therefore, the number format for the first number, 28.123, must be a
function of the range of Y-axis values; the format should not be a function
of that first number itself.

In which case, conditional formatting based on that number doesn't do me
much good.

Or am I missing something?

Charley

Jon Peltier said:
Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Charley said:
John,

I found a solution! As the data changes in my spreadsheet, the value axis
automatically changes its number of digits to the right of the decimal,
as needed.

The solution does require a five-line UDF. But that's much more efficient
than launching a macro that loops through every chart in my workbook,
setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next
week and post it at ExcelUser.com.

Thanks for your help.

Charley
Click to expand...
Click to expand...
 
Charley -

You ought to be able to write a formula to construct text labels in an
extra range of cells. Then follow John's suggestion, and apply them as
data labels to a dummy series along the axis. The procedure's also
written up here, if you want to see an example:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Charley said:
Jon,

Okay, I've been playing with this idea. It's a little more complex than it
first appears. If the data ranges from 29.123 to 0, the chart's value-axis
labels need to range from about 35 to 0. No decimals need be shown.

However, if the data ranges from 29.123 to 29.45, the value axis needs to
range from about 28.9 to 29.5.

Therefore, the number format for the first number, 28.123, must be a
function of the range of Y-axis values; the format should not be a function
of that first number itself.

In which case, conditional formatting based on that number doesn't do me
much good.

Or am I missing something?

Charley

Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Charley said:
John,

I found a solution! As the data changes in my spreadsheet, the value axis
automatically changes its number of digits to the right of the decimal,
as needed.

The solution does require a five-line UDF. But that's much more efficient
than launching a macro that loops through every chart in my workbook,
setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next
week and post it at ExcelUser.com.

Thanks for your help.

Charley


message

Charley,

Outside of using VBA, you could try deleting the Y-axis labels and adding
a
dummy Y-axis in it's place. If you were using an XY-Scatter chart, the
dummy
Y-Axis would consist of columns 1 and 2 below. Column three is a direct
reference to column 2 (for example C1=B1, C2=B2, etc.). Format column 3
as
TEXT.

Use a chart labeling add-in such as Rob Bovey's X-Y Labeler or John
Walkenbach's Chart Tools to set up a reference to the TEXT formatted data
labels. When complete, each Y-axis data label will appear in the same
format
as the data in column 3 below.

0 1.09 1.0856
0 2.01 2.01
0 3.01 3.011
0 4.01 4.01056
0 3.02 3.02
0 6.01 6.014

Regards,
John Mansfield
http://www.pdbook.com


:



I'm looking for an automatic way to format the value axis with the least
number of digits possible after the decimal. I want the format to switch

from #,##0 to #,##0.0 to #,##0.00, etc. as needed.

That is, I never want the Y axis to display the same number twice,
because
that means we need to add more digits to the right of the decimal. And I
never want the Y axis to display "0" in every right-most character after
the
decimal, because that means we need to reduce the number of digits
displayed.

Other than using macros, can anyone suggest a way to display the least
number of digits after the decimal?

Thanks.

Charley
Click to expand...
Click to expand...
Click to expand...
 
Jon,

I like the approach you show in your example, but it solves a different
problem than the one I'm facing.

If I understand your example correctly, your labels aren't in sync with
chart's initial Y-axis labels. Most importantly, the technique doesn't label
the top and bottom borders of the chart.

To accomplish these tasks, I need to use VBA to find the chart's MajorUnit
value. Unfortunately, when I return this value using a UDF, the function
conflicts with a 3rd-party program and crashes Excel. I've gotten around
this problem by using a short Sub to call the 3rd-party program and then the
routine to fix the Y axis. It's not pretty, but it works.

Thanks.

Charley

Jon Peltier said:
Charley -

You ought to be able to write a formula to construct text labels in an
extra range of cells. Then follow John's suggestion, and apply them as
data labels to a dummy series along the axis. The procedure's also written
up here, if you want to see an example:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Charley said:
Jon,

Okay, I've been playing with this idea. It's a little more complex than
it first appears. If the data ranges from 29.123 to 0, the chart's
value-axis labels need to range from about 35 to 0. No decimals need be
shown.

However, if the data ranges from 29.123 to 29.45, the value axis needs to
range from about 28.9 to 29.5.

Therefore, the number format for the first number, 28.123, must be a
function of the range of Y-axis values; the format should not be a
function of that first number itself.

In which case, conditional formatting based on that number doesn't do me
much good.

Or am I missing something?

Charley

Charley -

How many different variations will you have? You can use custom number
formats to get three different formats. I used this custom format:

[<10]0.00;[<100]0.0;0

to show the (random) numbers in the first column below as those in the
second column:

770.1263105 770
621.8223517 622
128.6427587 129
611.8604412 612
292.7198782 293
1.32507865 1.33
34.73011854 34.7
5.626873794 5.63
82.6602261 82.7
27.21322802 27.2
8.224506381 8.22
4.990436843 4.99
3.193506214 3.19
5.537794613 5.54
8.794161823 8.79

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Charley Kyd wrote:

John,

I found a solution! As the data changes in my spreadsheet, the value
axis automatically changes its number of digits to the right of the
decimal, as needed.

The solution does require a five-line UDF. But that's much more
efficient than launching a macro that loops through every chart in my
workbook, setting the number format of each value axis.

The solution is sort of a kludge, but it works. I'll write it up next
week and post it at ExcelUser.com.

Thanks for your help.

Charley


message

Charley,

Outside of using VBA, you could try deleting the Y-axis labels and
adding a
dummy Y-axis in it's place. If you were using an XY-Scatter chart, the
dummy
Y-Axis would consist of columns 1 and 2 below. Column three is a
direct
reference to column 2 (for example C1=B1, C2=B2, etc.). Format column
3 as
TEXT.

Use a chart labeling add-in such as Rob Bovey's X-Y Labeler or John
Walkenbach's Chart Tools to set up a reference to the TEXT formatted
data
labels. When complete, each Y-axis data label will appear in the same
format
as the data in column 3 below.

0 1.09 1.0856
0 2.01 2.01
0 3.01 3.011
0 4.01 4.01056
0 3.02 3.02
0 6.01 6.014

Regards,
John Mansfield
http://www.pdbook.com


:



I'm looking for an automatic way to format the value axis with the
least
number of digits possible after the decimal. I want the format to
switch

from #,##0 to #,##0.0 to #,##0.00, etc. as needed.

That is, I never want the Y axis to display the same number twice,
because
that means we need to add more digits to the right of the decimal. And
I
never want the Y axis to display "0" in every right-most character
after the
decimal, because that means we need to reduce the number of digits
displayed.

Other than using macros, can anyone suggest a way to display the least
number of digits after the decimal?

Thanks.

Charley
Click to expand...
Click to expand...
Click to expand...
 
Back
Top