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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: