Breakeven Chart with Dynamic Label
A recent post to the Microsoft Excel Charting newsgroup asks "I have a chart where two lines intersect at the breakeven point. How can I show a dynamic label at the point of intersection that describes the breakeven point?"
A simple solution is to add a text box in the chart. Make the source of that text box a worksheet cell that contains the data that you want to show.
For example, the model below provides the data needed to create a breakeven chart.
Based on the information above, a combination Line - XY Scatter Chart can be created to show the breakeven point.
When changes are made to the model, the label at the top of the chart will always reflect those changes. To create the label, first decide what worksheet cell you want the label to tie to. In this case the label ties to cell C25. Within that cell you can reference what is already there or create a phase that you want to appear. In this example the phrase "BE Units = 152.16 and BE Cost = $4,868" is returned by using the following formula in cell C25:
="BE Units = "&TEXT(F5,"##.##")&" and "&"BE Cost = "&TEXT(G5,"$#,###")
Finally, add the label to the chart. To do so, select the chart (select any element except a text item). Click in the formula bar, then type the number if it's static or type = and click on the cell with the value (no need to create the textbox first). Press enter and a textbox should appear in the middle of the chart. This will put a textbox in the chart.
Note: Instructions for adding the text box to the chart was provided by Jon Peltier of Peltier Technical Services via a past post to the Microsoft Excel Charting Newsgroup.
