Solved

How to highlight an Excel graph lines crossing?

Posted on 2016-09-29
9
119 Views
Last Modified: 2016-09-30
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!
0
Comment
Question by:philjans
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 2

Expert Comment

by:Saadia Attias
ID: 41821828
please post the file to answer you or print screen
0
 

Author Comment

by:philjans
ID: 41821855
Hi,
Here it is under the tab "English"
And the Graphic is "VÉ vs GAS".

Thanks!
0
 

Author Comment

by:philjans
ID: 41821861
Here's the kind of visual I'd like to get:
0
Office 365 Advanced Training for Admins

Special Offer:  Buy 1 course, get 2nd free!  Buy the 'Managing Office 365 Identities & Requirements' course w/ Accelerated TestPrep, and automatically receive the 'Enabling Office 365 Services' course FREE!

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 50 total points
ID: 41821872
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
 

Author Comment

by:philjans
ID: 41821873
... 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
 

Author Comment

by:philjans
ID: 41821900
Sometimes it looks like this:
expexch2.jpg
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41821929
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
 

Author Comment

by:philjans
ID: 41821950
@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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 450 total points
ID: 41822001
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question