How to highlight an Excel graph lines crossing?

I made a simple excel graph with 2 lines showing the cumulative costs of a gaz car costs and an electric car cumulative costs.

On the horizontal you see the years   (2016 2017 2018 2019 2020...)

And on the vertical line you see the cumulative costs ( 10,000 20,000 30,000 ....)

In this exemple, you see that the electric car starts more expensive but after 3 years, the line crosses the gas car line so you know that after 3 years, your return on investment pays off.

How can I have Excel do a vertical line pointing to the date the return on investment pays off?
Thanks!
philjansAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
For the series use, in plain speak as I don't have the ranges:

=IF(GarderValue>=SwitcherValue,GarderValue,"")

Put that under each value for the Garder/Switcher range of columns. That will however put values in each of the cells where Garder >= Switcher so will also need some sort of check whether the condition has already been met, assuming Garder in row 5, Switcher in row 7 and formula for comparison in row 12, all starting in column B, first formula in B12:

=IF(B5>=B7,B5,"")

Then in columns C to end of range:

=IF(COUNT($B12:B12)>0,"",IF(C5>=C7,C5,""))

With that copied across you will get a range with only one value; in the column where the values switch. Adding that series to the chart will put a line on the chart that goes along at zero and then peaks at the intersect. Select that series to then format it as a column chart and the line will change to one column. In the series settings there is the option for width and overlap of bars.

Alternatively, draw a pointing down arrow or vertical line in the format required and copy it to the clipboard. Select the column and press Ctrl + V. The arrow will then be superimposed over the column and the column formatting can then be set to No Fill and No Outline so you only see the arrow.

The line/arrow option will be less diagrammatically accurate in cases where the crossover is between series values.
0
 
Saadia AttiasNetwork & Security Engineer / Dev Pro AccessCommented:
please post the file to answer you or print screen
0
 
philjansAuthor Commented:
Hi,
Here it is under the tab "English"
And the Graphic is "VÉ vs GAS".

Thanks!
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
philjansAuthor Commented:
Here's the kind of visual I'd like to get:
0
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Are the progressions linear?  If so, one could determine the TREND of the two expense lines then calculate the intersection of the the two.  Then you would add a third category that plots a single, vertical line.
example graph
1
 
philjansAuthor Commented:
... this site still hard to figure out... I just posted 2 comments with 1 attachement each but I don't see any attachement here....
I'll try again to attach files...
Expert-Exchange-Ne-pas-ouvrir-pour-.xlsx
expert-exchange-2016-09-29-11_06_06-.jpg
0
 
philjansAuthor Commented:
Sometimes it looks like this:
expexch2.jpg
0
 
Rob HensonFinance AnalystCommented:
Add a 3rd series to the chart data which compares the two values for series 1 & 2. For the screen shot above you would want the value to appear in the cell where Garder >= Switcher.

This series can then be converted to a column chart rather than a line and set to be show a very thin column.
0
 
philjansAuthor Commented:
@Rob:
I don't understand...
If I do a "Garder"-"switcher" I get this:
expExch2.jpg
And if I do an "IF 0 and 1 I get this:
expExch3.jpg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.