Hello,
I have a chart that I am trying to scale based on the lowest value for the
category axis. I find the minimum value and then find the major unit and
divide it by 2. I set the min value (XMin) as being half the major unit
below the min value and then set the min scale to that value. Problem is
that I get long numbers sometimes (3.44444333, for example). I would like
to round that number down based on the number of the decimal places in the
major unit for the category axis. Is that possible? The code I have is:

MU = ActiveChart.Axes(xlCategory).MajorUnit / 2
XMin = Application.Min(database)
XMin = XMin - MU
ActiveChart.Axes(xlCategory).MinimumScale = XMin

Thanks much.

Bill

Bill,

This is an extract from a much larger routine. It sounds like you can handle
the rest of the logic, variable declaration etc. The values I have chosen
are arbitrary but work for me.

with MyChart 'set a reference to your chart

'laxiscounter is a counter for the number of axes that runs within a
bigger loop in my routines

'dMaxVal is the max value of any data point, dMinVal is the min
value of any data point
'on the axis, you say you already have this
dSpread = Abs(dMaxVal - dMinVal)

'set number format for axis
On Error GoTo NoNumberFormat 'you need an error handler for this
strOrigFormat = .Axes(xlValue, lAxisCounter).TickLabels.NumberFormat
On Error GoTo SmartScaleChart_Error

'some charts use percents
If InStr(strOrigFormat, "%") 0 Then
Select Case dSpread
Case Is <= 0.0003
strNumFormat = "#,##0.000%"
Case 0.0003 To 0.005
strNumFormat = "#,##0.00%"
Case 0.005 To 0.06
strNumFormat = "#,##0.0%"
Case Is 0.06
strNumFormat = "#,##0%"
End Select
Else
'otherwise it's numeric
Select Case dSpread
Case Is < 0.03
strNumFormat = "#,##0.000"
Case 0.03 To 0.5
strNumFormat = "#,##0.00"
Case 0.5000001 To 6
strNumFormat = "#,##0.0"
Case Is 6
strNumFormat = "#,##0"
End Select
End If

'add a currency symbol if needed
If InStr(strOrigFormat, "$") 0 Then _
strNumFormat = "$" & strNumFormat

'here's the bit that figures out the optimum minimum
bForceZeroLimit = (dMinVal 0)
dMinVal = dMinVal - (dSpread / 10)
If bForceZeroLimit And dMinVal < 0 Then dMinVal = 0

'set min val for axis
If dMinVal < 0 Then
dMinVal = Application.RoundUp( _
dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0)))
Else
dMinVal = Application.RoundDown( _
dMinVal, 2 - Len(Application.RoundDown(dMinVal, 0)))
End If

.Axes(xlValue, lAxisCounter).MinimumScale = dMinVal

End If

.Axes(xlValue, lAxisCounter).TickLabels.NumberFormat = strNumFormat

end with

Robin Hammond
www.enhanceddatasystems.com


"Bill" wrote in message
hlink.net...
Hello,
I have a chart that I am trying to scale based on the lowest value for the
category axis. I find the minimum value and then find the major unit and
divide it by 2. I set the min value (XMin) as being half the major unit
below the min value and then set the min scale to that value. Problem is
that I get long numbers sometimes (3.44444333, for example). I would like
to round that number down based on the number of the decimal places in the
major unit for the category axis. Is that possible? The code I have is:

MU = ActiveChart.Axes(xlCategory).MajorUnit / 2
XMin = Application.Min(database)
XMin = XMin - MU
ActiveChart.Axes(xlCategory).MinimumScale = XMin

Thanks much.

Bill

Robin,
Thanks a lot. I appreciate it!

Bill


*** Sent via Developersdex http://www.developersdex.com ***