Solved

How could I force a 45 degree angle line in an excel scatter plot

Posted on 2010-11-15
12
12,519 Views
Last Modified: 2012-05-10
How could I force a 45 degree angle line in an excel scatter plot instead of a trend line??
0
Comment
Question by:libertyforall2
  • 7
  • 4
12 Comments
 
LVL 4

Expert Comment

by:tzwimfam
ID: 34142662
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34143711
Hello,

create a second data series for the 45 degree line, for example

X   Y
0   0
1   1

Add this series to your scatter plot, then send it to the secondary axis and set the min to 0 and max to 1 on both the secondary X and the secondary Y axis. Format with line and no data markers.

cheers, teylyn
0
 

Author Comment

by:libertyforall2
ID: 34149942
"send it to the secondary axis" How do I do this?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:libertyforall2
ID: 34149991
I took a look at the link at is says In A1: =ROW()
In B1: =ROW()
copy A1 and B1 down, let's say 20 rows.
Select A1:B20, Insert>Graph and choose a line graph

This is not helpful. When I click on A1 and enter =ROW() and then I try to go to B1 it stays in A1 and turns into =ROW()+B1. If my data is in column C & D. How would I do this? It also says choose a line graph, but I need a scatter plot not a line graph. I just need a line within the scatter plot.
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34151301
Hello,

>> "send it to the secondary axis" How do I do this?

I take it that adding the new series to the chart is not the problem? After doing that, select the newly added series, then open the Format dialog, e.g by right-clicking and selecting "Format Series".

In Excel 2003 the command is on the Axis tab. Select the Secondary Axis radio button.
In newer versions, it is on the Series Options tab.

With the new series on the secondary axis, format the secondary Y axis scale to be minimum 0 and maximum 1. Then you need to switch on the secondary X axis temporarily, to be able to format it:

XL 2003 and earlier: Chart > Chart Options > Axes tab > tick the Secondary > Value (X) axis box
XL 2007 and later: Chart Tools > Layout > Axes button > Secondary Horizontal axis > Show default axis.

Since the commands are so different in various Excel versions, it is always good to know what version of Excel you're working with.

If this comment does not help you solve your issue, please let us know what your Excel version is.

cheers, teylyn

0
 

Author Comment

by:libertyforall2
ID: 34157753
Excel for Mac 2008 version 12.2.7 (100910)
0
 

Author Comment

by:libertyforall2
ID: 34158226
I have attached my excel file with the data. I am can you should me what needs to be done? I am unclear on exactly how to spread the 0 & 1 data points in the column to create the line. Also, I do not want my domain & range to necessarily be one.
so4konascatter12.xls
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 34159295
Hello,

I have no clue what you mean with "domain & range".

See attached for your updated chart. I applied exactly the steps I described above. Excel 2008 for Mac is similar to Excel 2007.

I left the secondary axes visible, but you probably want to hide them. Click each of the secondary axes, then open the formatting dialog and set

Major tick mark type = none
Minor tick mark type = none
Axis labels = none

cheers, teylyn
Copy-of-so4konascatter12-1.xls
0
 

Author Comment

by:libertyforall2
ID: 34159793
Ok. The domain and range is the minimum to maximum values of X & Y. The line looks good but how would I get rid the the vertical & horizontal secondary axis text boxes? on the top and right of the chart graph?
0
 

Author Comment

by:libertyforall2
ID: 34159820
When I try to delete the secondary horizontal & vertical value axis the line moves. IT is possible to remove this without moving the line? Otherwise the numbers will be confusing.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 500 total points
ID: 34159844
You don't want to delete the actual axes, just make them invisible. I described above how to format the axes to hide the tick marks and labels. You can also set the axes lines to "none". I don't know which text boxes you are referring to, but if it's the axes titles, you can just hide them, too. Use the Layout ribbon, click Axis Titles, select the axis in question and select "none".

cheers, telyn
0
 

Author Closing Comment

by:libertyforall2
ID: 34160346
Got it. Perfect!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

838 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