Solved

How to highlight an Excel graph lines crossing?

Posted on 2016-09-29
9
42 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
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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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 31

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now