I've got a feeling this will have been an FAQ at some point, so apologies if
Prompted by a colleague (I had never really noticed this before), I have
played a bit displaying a simple exponential function [y = A.exp(-b.t)],
changing A and b to change the range of x,y pairs. I created an XY scatter
chart, with y axis set to logarithic scale. Excel (set to autoscale the y
axis) always seems to use one more decade than is necessary (usually at the
low end). For example, when the first data point (x,y) is (2, 2011) and the
last (20, 54.9) Excel uses 4 cycles (ranging from y = 10000 to y =1) when 3
cycles would be more than adequate (y min = 10). In fact, this problem
causes the data to be more compressed than it need to be, which is an issue
if you want to do manual curve fitting, etc. What gives?
Thanks in advance
XL insists on using factor of 10 for the min./max. values. It is
possible to simulate the effect of other values. Check the 'flexible
log scale' page in my web site.
MVP MS Excel
In <#4P0Y6JYBHA.2244@tkmsftngp03>, Phil C <Phil.C...@ulh.nhs.uk>
I am aware of the y axis settings. If I set y min to 10, this automatically
turns off the autoscale. If I then change the data so that some y-values are
less than 10, they are simply not displayed (as expected, since y-min is
fixed at 10). If autoscale feature is re-applied (checkbox), the minimum
value resets iself to 1, because it can 'see' some data points with y values
between 1-10. The problem is that if the data is now changed so that there
are no y values between 1-10 Excel stubbornly persists in maintaining the
minimum y value at 1 when, to my mind, it should readjust to 10 (or whatever
new minimum value is appropriate for the data being displayed).
Y min seems to be 'anchored' at whatever nadir was reached up to that point.
This can lead to one, or perhaps two, cycles being redundant cycle at the
bottom of the chart with no data points in them.
Slightly laboured, I know, but I hope that's clearer.
"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
I've seen the stubbornness of Excel to fix an auto axis minimum equal to 1,
but I'd never noticed such a detailed conspiracy on Excel's part. At least
not on such an item as an axis scale parameter <g>. I almost always set my
scales manually, even if I have to keep doing it again and again. On
occasion I even use a workbook change event to recalc the scale the way I
like, if I get ambitious.
In article <eGdqASLYBHA.1160@tkmsftngp03>, Phil C said...
In <eGdqASLYBHA.1160@tkmsftngp03>, Phil C
Thanks again. I've read the Helpfiule associated with your add-in and it
sounds useful. Before installing could you confirm that it will work OK with
version 2002? Your site says Excel version 2000 only.
Tushar Mehta <ng_p...@bigfoot.com> wrote in message
FWIW, when I created the add-in and published it to the web page, XP was
not yet available ;-)
In <u#3S1ISYBHA.2060@tkmsftngp07>, Phil C <Phil.C...@ulh.nhs.uk>
Seems to me, Excel 4 or 5 had what I considered a nicer auto scale algorithm.
Or else I'm getting pickier in my old age. Over the years I've constructed
a few axis scaling routines. I should put them together and offer up the one
I like best. And yours is similar to mine, inelegant but useful.
In article <MPG.1647e6885...@msnews.microsoft.com>, Tushar Mehta
Maybe we should share the algorithms we like.
In <OTBMdJoYBHA.1744@tkmsftngp03>, Jon Peltier <jonpe...@yahoo.com>
> In the second, which I haven't completed, develop a 'good' algorithm to
> specify the min./max. values.
> Maybe we should share the algorithms we like.
Here's the one I use (for linear plots - you'd have to adjust it for log plots):
dMin As Double
dMax As Double
dScale As Double
Function fnGraphScale(ByVal dMin As Double, ByVal dMax As Double) As typChartScale
'* Function Name: fnGraphScale
'* Inputs: dMin - The minimum value to occur on the chart
'* dMax - The maximum value to occur on the chart!
'* Outputs: Returns a user-defined type, giving the minimum, maximum and
'* major-unit scales to set the axes to.
'* Purpose: Calculates tidy settings for the chart axes.
Dim dPower As Double, dScale As Double
'Check if the max and min are the same
If dMax = dMin Then
dScale = dMax
dMax = dMax * 1.01
dMin = dMin * 0.99
'Check if dMax is bigger than dMin - swap them if not
If dMax < dMin Then
dScale = dMax
dMax = dMin
dMin = dScale
'Make dMax a little bigger and dMin a little smaller (by 1%)
If dMax > 0 Then
dMax = dMax + (dMax - dMin) * 0.01
dMax = dMax - (dMax - dMin) * 0.01
If dMin > 0 Then
dMin = dMin - (dMax - dMin) * 0.01
dMin = dMin + (dMax - dMin) * 0.01
'What if they are both 0?
If (dMax = 0) And (dMin = 0) Then dMax = 1
'This bit rounds the maximum and minimum values to reasonable values
'to chart. If not done, the axis numbers will look very silly
'Find the range of values covered
dPower = Log(dMax - dMin) / Log(10)
dScale = 10 ^ (dPower - Int(dPower))
'Find the scaling factor
Select Case dScale
Case 0 To 2.5
dScale = 0.2
Case 2.5 To 5
dScale = 0.5
Case 5 To 7.5
dScale = 1
dScale = 2
'Calculate the scaling factor (major unit)
dScale = dScale * 10 ^ Int(dPower)
'Round the axis values to the nearest scaling factor
fnGraphScale.dMin = dScale * Int(dMin / dScale)
fnGraphScale.dMax = dScale * (Int(dMax / dScale) + 1)
fnGraphScale.dScale = dScale
Microsoft MVP - Excel