Chart Primary Y axis scale variation correction by macro for better readbility

S

Sekars

New member
Joined
Jul 13, 2016
Messages
14
Reaction score
0
Points
0
Hello Friends


Please find the attached excel 2010 version file (Chart Primary Y axis scale variation correction.xlsx)


In the Sheet `Sheet1' Cell X17 change the dates 01-Oct-81 or 01-Jan-82 and notice the chart Y Primary Axis scale changing.


If the input is 01-Oct-81 then Primary Y axis scale is 390 to 460 which is much much readable.


But if the input is 01-Jan-82 then Primary Y axis scale is 0 to 450 which is not much readable and why the scale is


not between say 300 to 420 which will make better readbility. So in Cells Y2 and Y3 I plotted the max.


and min Primary Y Axis scales by formula, Is there any way to use macro to input these values in chart ?


thanks in advance
 

Attachments

  • Chart Primary Y axis scale variation correction.xlsx
    217 KB · Views: 15
In the attached there's a macro in Sheet1's code-module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("x17")) Is Nothing Then
  With ChartObjects("Chart 1").Chart.Axes(xlValue)
    .MinimumScale = Range("Y3").Value
    .MaximumScale = Range("Y2").Value
  End With
End If
End Sub

Notes:
MicroSoft has a rule about Y-axis minima: If Ymin is less than 5/6 of the Ymax, the automatic minimum Y axis scale value is zero.
Your formula in cell Y3 took the average instead of the minimum! I've amended that.
I've added FLOOR and CEILING functions to those formulae to clean up how the values on the y-axis are shown (removes all the decimals).
The use of FLOOR and CEILING might obviate the use of */1.03 in you formulae - I've added them in cells immediately to the right for your consideration.
I would've liked there to be a non-macro solution and I haven't quite given up on that possibility yet.
 

Attachments

  • ExcelGuru6382Chart Primary Y axis scale variation correction.xlsm
    224.6 KB · Views: 13
Last edited:
Hello p45cal

Thanks for your VB code.

Your descriptions about the formula and the `MicroSoft rule about Y-axis minima' is looking good.

But even after including the macro chart curves are not visible it is in up or down.

How to make the curve inside the chart area and to make the chart as better readability,

Regards

Sekars
 
Sekars said:
How to make the curve inside the chart area and to make the chart as better readability,
Click to expand...
Let's take this one step at a time:
1. Confirm that you opened the file attached to msg#2 of this thread, allowed macros to run, and when you changed the date in cell in cell X17, the chart lines went off the scale and the chart was not easily readable.

I'll give you a clue: I've just done the above and the chart remains very readable.
 
Hello p45cal

Excellent thanks

thanks again
 
Hello p45cal

Sure, in future i will provide the link while cross posting.

Also in the real application i need to change the sheet name `Sheet1' to `Process' and i need to move the `Chart 1' from `Sheet 1' to separate chart sheet named `Chart'.

`Sheet 1' Cell X17 will be `Process' Cell C2
`Sheet 1' Cell Y2 will be `Process' Cell Y2 (No change in cell address)
`Sheet 1' Cell Y3 will be `Process' Cell Y3 (No change in cell address)
`Sheet 1' `Chart 1' will be moved to new chart sheet and the sheet name is `Chart'.

For this how to modify your VBA code.

thanks for your kind help.
 
The code needs to be in the sheet Process' code-module with 2 small tweaks. See attached.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
  With Sheets("Chart").Axes(xlValue)
    .MinimumScale = Range("Y3").Value
    .MaximumScale = Range("Y2").Value
  End With
End If
End Sub
 

Attachments

  • ExcelGuru6382Chart Primary Y axis scale variation correction.xlsm
    228 KB · Views: 8
You must log in or register to reply here.
Back
Top