[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to highlight an Excel graph lines crossing?

Posted on 2016-09-29
9
Medium Priority
?
292 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
New style of hardware planning for Microsoft Exchange server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

656 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