I read this message on the Excel-G discussion list:
Excellists
I have a line chart with two series of points (5000 in each) and the value points (Y-axis) are concentrated in the middle region of the axis.
I would have more focus in the middle of the Y-axis, to show the differences between series.
It is possible build the axis wit two different scales, one in the middle (like a magnifying lens) and the other in both ends?
Sorry if I can’t send the chart in annex.
Thank You
Aldo Cosentino
After a bit of thought I replied as follows:
I hope I have understood this Aldo
I have created a chart with just 12 data points for two variables rather than 5,000 data points, to keep it simple.
I calculated the average for each variable
I set up a new, highlight, column for each variable by entering the following formula into the appropriate cells: =IF(AND(B4/average>=0.8,B4/average<=1.2),B4,NA()) … that is, I am highlighting the range 80% – 120% of the average for each variable and you can change these as you wish … these are the highlights of the Y axis you mentioned
I then created an XY Scatter chart for the X axis and the four Y variables: data1 and highlight1; data2 and highlight2
If you wish I can send the file I created to you … off list
My further thought is that a statistical process control chart is the kind of thing that Aldo may be looking for.
I prepared an Excel workbook to simulate what I think Aldo needed, as follows:
There is a similar range for Highlight 2, relating to a second product.
The range C9:C21 contains the formula
=IF(AND(B4/average>=0.8,B4/average<=1.2),B4,NA())
that I mentioned in my reply to Aldo. Note the use of the NA() function … what is it and what is it doing?
I prepared three charts, as follows:
Chart 1: this shows Product 1 sales data, blue diamond markers and those sales values that fall in the middle of the sales data, red square marker. I chose the four middle range values by telling Excel to choose any value that is greater than or equal to 80% of the average and less than or equal to 120% of the average … using that formula in column C.
What is happening here is that the blue diamond markers show all of the sales data; and the red square markers show an additional series of data, selecting just the ≥80% and ≤120% data points. Where did the 80% and 120% come from? I just decided on 80% and 10% out of the blue … see below for an alternative approach to that.
Chart 2: In this second chart, I have chosen and used exactly the same inputs as for chart 1. The difference here, though, is that I selected the highlight 1 data points and changed the chart type for them to 2D Column Chart.
Chart 3: this is the same as charts 1 and 2, essentially, except that I have made it a more formal Statistical Process Control style chart. What that means is that I have left the two data series alone, the same as charts 1 and 2. However, I have added the average line, the average plus one standard deviation (σ) line and the average minus σ line. Read about SPC charts and what we are doing here, then, is to show the average, upper and lower warning/action lines.
Although I have left the highlight 1 data series on the chart, I could easily delete them now. What that means is that we forget the ≥80% and ≤120% data points and concentrate on the data points that fall between the upper and lower warning/action lines.
Please note, I could have made the upper and lower warning/action lines range from ≥80% and ≤120% but I used the more formal ±σ
Here is the Excel file on which the above is based: highlight_y
Duncan Williamson
Excel with Excel Master 