Problem with autoscale of log plots?

[Phil C]

Hi All

I've got a feeling this will have been an FAQ at some point, so apologies if
so...

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

Phil

[Tushar Mehta]

Double-click the y-axis. In the Format Axis dialog, in the Scale tab,
set the Minimum value to 10.

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.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--

In <#4P0Y6JYBHA.2244@tkmsftngp03>, Phil C <Phil.C...@ulh.nhs.uk>
wrote

[Phil C]

Tushar, thanks for the response, but I may not have explained myself
properly....

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.

Regards, Phil

"Tushar Mehta" <ng_p...@bigfoot.com> wrote in message
news:MPG.16475cb67...@msnews.microsoft.com...

[Jon Peltier]

Phil -

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.

- Jon

In article <eGdqASLYBHA.1160@tkmsftngp03>, Phil C said...

[Tushar Mehta]

Yeah, XL does not the most sophisticated mechanism for dealing with axis
ranges. You might want to check out a free add-in, Auto Chart Manager,
from my web site. It links the axis min./max. values to worksheet
cells. Not the most elegant of solutions, but it works.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--

In <eGdqASLYBHA.1160@tkmsftngp03>, Phil C
<phi...@cosgriff.freeserve.co.uk> wrote

[Phil C]

Tushar

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.

Regards, Phil

Tushar Mehta <ng_p...@bigfoot.com> wrote in message

news:MPG.1647e6885...@msnews.microsoft.com...

[Tushar Mehta]

I just did some tests with XL2002 and it worked just fine. Of course,
that doesn't guarantee that you won't find a bug in it <g>

FWIW, when I created the add-in and published it to the web page, XP was
not yet available ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--

In <u#3S1ISYBHA.2060@tkmsftngp07>, Phil C <Phil.C...@ulh.nhs.uk>

[Jon Peltier]

Tushar -

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.

- Jon

In article <MPG.1647e6885...@msnews.microsoft.com>, Tushar Mehta
said...

[Tushar Mehta]

Yeah, I decided to tackle the problem in two steps. In the first,
automate the axis min/max values by linking them to a worksheet cell.
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.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
MVP MS Excel
--

In <OTBMdJoYBHA.1744@tkmsftngp03>, Jon Peltier <jonpe...@yahoo.com>
wrote

[Stephen Bullen]

Hi Tushar,

> 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):

Type typChartScale
dMin As Double
dMax As Double
dScale As Double
End Type

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
End If

'Check if dMax is bigger than dMin - swap them if not
If dMax < dMin Then
dScale = dMax
dMax = dMin
dMin = dScale
End If

'Make dMax a little bigger and dMin a little smaller (by 1%)
If dMax > 0 Then
dMax = dMax + (dMax - dMin) * 0.01
Else
dMax = dMax - (dMax - dMin) * 0.01
End If
If dMin > 0 Then
dMin = dMin - (dMax - dMin) * 0.01
Else
dMin = dMin + (dMax - dMin) * 0.01
End If

'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
Case Else
dScale = 2
End Select

'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

End Function

Regards

Stephen Bullen
Microsoft MVP - Excel