Excel Chart: Always Markup the Cross Points of Two Line Charts

Click here to see Alphabetical Content of all posts 

[Notice: The chart in the post is created on Excel 2010] 

Last week I posted an article about "Markup the Maximal and Minimal Value from a Line Chart". Some friends asked if we could markup the cross points of two or multiple line charts in Excel. Here is an example from The Economist. A black dot highlights an important cross point of three line charts. This question is a little interesting, for this case we can do it manually and easily. But How to markup all cross points automatically in Excel?

After some practice, I find it can be created perfectly in Excel. But it needs a little mathematic knowledge. I prepare some raw datas and record my creating process step by step. The finial chart should like this.

Prerequest: Suppose a company has two departments and the output of each department is different with each other. We need highlight when they have the same output from line chart report.

Select Column A2:C13 and insert a line chart from ribbon bar into your week sheet. Your chart should like this:

Step 1: Assign the line chart a virtual X number value.

As we know, the horizontal axis for line chart only stands for categories in Excel. It is not a real number value. In order to add cross point, we need assign a virtual number value to them. For this example, the horizontal axis stands for 12 months, so we can assign 0 - 12 to it, which matches 12 tick marks on the horizontal axis. We've also found that the first point of both chart are located at the center of the first category "Jan". All other categories are also located at the center position of every category. In other words, if we assign 0-12 to these horizontal categories, the X value of the first point should be 0.5 and the second should be 1.5... Let's save them in column E.

Step 2: Does there exist cross point for each category?

If the data meets the following 3 conditions, we say there is a cross point for these two lines in the category.

	Case A: The current Y value of line A is greater than that of line B but the next Y values are reversed. An example is the category "Jan". For this example, we can use a formula to stand for this case. (B2>C2)*(B3<C3)
	Case B: The current Y value of line A is smaller than that of line B but the next Y values are reversed. An example is the category "Feb". For this example, we can use a formula to stand for this case. (B2<C2)*(B3>C3)
	Case C: Case C is simple, if both values are equal with each other; we say they have cross point. For example, category "Apr" and "Dec". We can also use a formula to stand it (B2=C2)

Combine above conditions; we can get an integrated formula to check if there is a cross point for each category. If there is a cross point, the formula will return true, or it will return false. The formula can be written as: 

=IF(((B3>C3)*(B4<C4))+((B3<C3)*(B4>C4))+(B3=C3),TRUE,FALSE)

Input this formula to column F and drag to fill F2:F12, we can get if there are cross points in all categories.

Notice: There is a special case. Because there is no data in F14, for F13, we cannot apply the above formula. It's an independent cell; we say there is cross point only when they are equal with each other. We can input the following formula into it like this.

=IF(B14=C14,TRUE,FALSE)

OK, you've got all information about if there is a cross point in a category. Please see column F from the previous data table.

Step 3: Calculate the X value for all cross points

We've got if there exist cross point for every category. But we cannot say all cross points are also located at the center of this category. We have to calculate X value for all cross points by ourselves. At this point, we need a mathematic knowledge. In math, we can use the following formula to get a cross point of two line.

Suppose two points (x1,y1) and (x2,y2) make a line and the point (x3,y3) and (x4,y4) make another one. Suppose they have a cross point and the cross point is (a,b), we can use the following two formula to get value for a & b.

(b-y2)/(a-x2)=(y1-y2)/(x1-x2)
(b-y4)/(a-x4)=(y3-y4)/(x3-x4)

Here I give out the final formula is Excel directly. You can research the above formula if you are interesting about it. All X values are saved into column G. If there is no cross point in the category, we will mark it as NA().

=IF(F3,(E4*B3-E3*B4-E4*C3+E3*C4)/(C4-C3-B4+B3),NA())

Notice: we also need to process the special case G13. The X value of G13 should be.

=IF(F14,E14,NA())

Step 4: Get Y value for all cross points

If we've got all X value for the cross points, we can apply them into the above mathematical formula and get Y values for these points. You need mathematical knowledge again. Here I give out Excel formula directly too. Fill column H with the following formula and process the special case for H13, you can get the correct result.

Formula in the cell H2:H12

=((B4-B3)*G3+E4*B3-E3*B4)/(E4-E3)

Formula in the cell H13

=IF(F14,B14,NA())

Step 5: Continue creating chart

God, mathematic also makes me crazy. But we've got all auxiliary data. Let's go back to that line chart. Select column H (H2:H13) and press Ctrl+V to copy it to clipboard and then select that draft chart and press Ctrl+V to paste data for cross points into chart.

 

Step 6: Change the chart type of the new added series to "Scatter"

Right click the new added series and select "Change Series Chart Types" from the context menu. Choose "Scatter" to change it to a scatter chart.

 

Step 7: Change the X value for the scatter chart

By default the X value for this series is column A, which means all cross point will be located at the center of every category. It's not our expectation. Right click the new scatter chart and choose "Select data" from context menu and change X value from column A to column G.

 

Reselect Column G as the X for the scatter chart.

Until now, we've add all data into the chart correctly. But the cross points are still not located correctly.

Step 8: Format the secondary axis to position scatter to correct location

Maybe you've found the reason why the cross points have not been located correctly. The maximal value for the secondary horizontal axis and the secondary vertical axis are not the same as the primary one, and this causes error position of the cross points. Change the maximal value the secondary horizontal axis to 12 and vertical one to 1000. (Notice, this value should be same as that from the primary axis.) Cool, all cross points are located to correct position automatically. The chart looks better than the draft one.

Beautify the result chart

Let's remove all secondary axes and change the color and theme for chart background and lines. Add some data label for cross points. A professional line chart with highlighted cross points appears.