Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations IDS on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

setting log scale 1

Status
Not open for further replies.

zappedagain

Electrical
Jul 19, 2005
1,074
I have a log-log plot where this data set starts at X=600 and goes up. I'd expect the log scale to start at 100 and go up from there 100, 1000, 10K, 100K...), but Excel always starts at 1 (1, 10, 100, 1000...). I can set it manually under "Format axis', but I'd rather not so when this spreadsheet is used with other data sets the plot doesn't drift off the graph.

Is there a trick for this? or will I need a macro/script to accomplish this? Thanks.

Z
 
Replies continue below

Recommended for you

Seems like you have three choices that you've already outlined:
1 - set plot scale manually (may not be ideal if data changes)
2 - leave plot scale in auto (may leave extra room at the bottom of the scale that you seem not to like)
3 - vba scribt to give something similar to auto-control, but more customized to your own preferences.

=====================================
(2B)+(2B)' ?
 
Here's a little macro I made that can create Log-Log graphs and Format the axis for the X & Y. If you want to add more data, you can re-run the macro to create a new graph. The macro assumes the data is in Col A & B for X & Y data respectively and starts at Row 2.
Code:
Sub log_log_chart()
'
Dim irow As Integer
Dim xmin, ymin
irow = 2
xmin = 1E+99
ymin = 1E+99
Do Until IsEmpty(Cells(irow, 1))
    If Cells(irow, 1) < xmin Then xmin = Cells(irow, 1)
    If Cells(irow, 2) < ymin Then ymin = Cells(irow, 2)
    irow = irow + 1
Loop

    Range(Cells(1, 2), Cells(irow - 1, 2)).Select
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlXYScatter
        .SeriesCollection(1).XValues = "='Sheet1'!$A$2:$A$" & irow - 1
        .Axes(xlValue).ScaleType = xlLogarithmic
        .Axes(xlValue).MinimumScale = 10 ^ Int(Log(ymin) / Log(10))
        .Axes(xlCategory).ScaleType = xlLogarithmic
        .Axes(xlCategory).MinimumScale = 10 ^ Int(Log(xmin) / Log(10))
    End With
    Range("A1").Select
End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor