?
Solved

How to highlight an Excel graph lines crossing?

Posted on 2016-09-29
9
Medium Priority
?
182 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 200 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 1800 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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

777 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