Jump to content


Photo

Illustrating inverse relationship between % and $ over time

Inverse relationship

  • Please log in to reply
3 replies to this topic

#1 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 05 November 2015 - 08:45 PM

Hi. I want to graph with a line an inverse relationship between an increasing percentage and a decreasing dollar amount over a period of years. A template to construct the data would be helpful. Some tips on formatting the line graph would also be helpful. The three players are years, percentage, and dollar amount. Thank you.

#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 05 November 2015 - 09:18 PM

Hi MikeyMike,

 

I think what you need is a chart with a secondary axis. This way you can plot the percentages and dollar amounts on separate axes, allowing the inverse relationship to be apparent even though the values are drastically different.

 

You can see how to create a chart with a secondary axis in the video tutorial: Essential Skills Lesson 5-23 Create a chart with two vertical axes.

 

I have attached an example based on your specifications:

 

Attached File  CorrelationChart.xlsx   14.02KB   112 downloads

Correlation.png

 

I hope this is the kind of chart you were looking for, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 MikeyMike

MikeyMike

    Member

  • Members
  • PipPip
  • 28 posts

Posted 05 November 2015 - 09:48 PM

Thanks. I would rather show it with just one line. How can I do that? Maybe I want to plot the relationship, inverse correlation. Is there a more statistical graph that uses just one line?

#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 845 posts

Posted 06 November 2015 - 12:53 PM

Hi again MikeyMike,

 

I am not sure how you could indicate an inverse relationship with just one line. By definition, an inverse relationship shows one value decreasing as another value increases (or vice-versa). I really can't think of a way that you could illustrate this without showing both values.

 

Perhaps I am misunderstanding what you're trying to do, so maybe you can explain further and provide an example of the data you're trying to plot?


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users