Decimals or not?

[Wil Moosa]

I have a series of cells which contain data to "feed" a chart. These data can contain decimals or not. If all data do not contain decimals I want the cell (and the chart) only to show the whole numbers. If there are decimals in just one of the data I want all to show a decimal.

What is best to do? Using vba-code or simply adding a formula to each of the cells?

 

[Juan Pablo González]

And is the number of decimals variable ? or how should that be calculated ?

 

[Wil Moosa]

Actually the chart is a multi purpose one. Some times the manual input might be plain numbers other times it might be currency ($ 12.30). The maximum of numbers is 12 (a year) and the height varies. The number of decimals is not calculated but plain input.

Important might be that the input is always manually added to twelve cells while the chart gets it's data nót from these cells but from 12 cells which are linked bij formula (=A1, =a3, =A5). This is done because the home cells are not following cells and the range could be defined more easily this way.

Am I talking sense?

 

[PaddyD]

"Am I talking sense"

Probably. Nevertheless, I'm a little confused.:


"If there are decimals in just one of the data I want all to show a decimal. "

Do you mean that you want the series to be formatted for decimals only if at least one item is a decimal?

 

[Wil Moosa]

Do you mean that you want the series to be formatted for decimals only if at least one item is a decimal?

Yes...

 

[PaddyD]

You'll need some vba to do that. not really my area of expertise, but I'm sure someone will help...

 

[kskinne]

what is the range of data that you want to test for decimals? and is your chart embedded as an object in an existing sheet, or is it in it's own sheet?

kevin

 

[Wil Moosa]

what is the range of data that you want to test for decimals? kevin

A49 to A60

and is your chart embedded as an object in an existing sheet, or is it in it's own sheet? kevin

I never get used to the terminology but I thínk it is in it's own sheet...

 

[kskinne]

there's probably a better way to do this, but give this a try:

Sub Test()
x = ""
For y = 49 To 60
If Cells(y, 1).NumberFormat Like "*.*" Then x = Cells(y, 1).NumberFormat
Next y
    If x = "" Then
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = "#,##0"
    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "#,##0"
    Range("A1").Activate
    Else
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.Axes(xlCategory).TickLabels.NumberFormat = x
    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = x
    Range("A1").Activate
    End If
End Sub