| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Least number of digits in Y-axis labels
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 |
|
#2
|
|||
|
|||
|
Charley,
Outside of using VBA, you could try deleting the Y-axis labels and adding a dummy Y-axis in its 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 Boveys X-Y Labeler or John Walkenbachs 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 "Charley Kyd" wrote: 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 |
|
#3
|
|||
|
|||
|
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 Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#4
|
|||
|
|||
|
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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#5
|
|||
|
|||
|
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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#6
|
|||
|
|||
|
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" wrote in message ... 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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#7
|
|||
|
|||
|
Your idea is particularly timely, because my UDF worked during test
but failed in practice. In fact, it keeps crashing Excel. 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 Kyd wrote: 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" wrote in message ... 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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#8
|
|||
|
|||
|
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" wrote in message ... 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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#9
|
|||
|
|||
|
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 Kyd wrote: 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" wrote in message ... 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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
|
#10
|
|||
|
|||
|
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" wrote in message ... 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 Kyd wrote: 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" wrote in message ... 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 "John Mansfield" wrote in 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 "Charley Kyd" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| print labels from excel with title and number | Excel Discussion (Misc queries) | |||
| How do I move the Category Axis labels outside of the Plot Area? | Charts and Charting in Excel | |||
| Y axis labels | Charts and Charting in Excel | |||
| Labels on Chart with Negative Value Axis | Charts and Charting in Excel | |||
| Linking Axis Labels | Charts and Charting in Excel | |||
Linear Mode
