Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


setting log scale

setting log scale

setting log scale

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.


RE: setting log scale

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)' ?

RE: setting log scale

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.


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

    Range(Cells(1, 2), Cells(irow - 1, 2)).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
End Sub 

RE: setting log scale

Very nice! Thanks.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close