×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Confused with a simple excel/graph issue
3

Confused with a simple excel/graph issue

Confused with a simple excel/graph issue

(OP)
Hi there, So to sum this up excel is confusing the hell out of me at the moment, I am attempting to make a P-v diagram in excel using a small amount of data recieved from a test. I will post what I am currently getting but basically all I need is for the graoh to plot point 1 at 0.001084 at 400kPa and point 2 at 0.01 at 2MPa, literally just showing a straight line but for some reason this is what I am getting and I have no idea why. Any help would appreciated. Pressur eon the x axis and volume on the y axis.

Update: I think I might have just fixed this by switching the data around so that volume is in the left column and pressure is on the right and just using the switch row/column button as I am now getting the correct graph.

RE: Confused with a simple excel/graph issue

Without seeing your actual spreadsheet(!) it's a bit of a guess, but the coloured highlighting on your table containing your source data seems to suggest something is awry in the data ranges you have selected.  Your two extraneous zeroes are coming from the fact that you have your column HEADINGS as one of your DATA ranges.  (And the fact that the other highlighted range is 2x2 rather than 1x2 is also probably not helping much.)

RE: Confused with a simple excel/graph issue

That's often common; Excel split your data into two series, with each column in its own series. One of the two series will have its cell references in the correct place; you need to copy the cell references from the other series and add it to the one with the correct references. Then delete the extraneous series

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert! https://www.youtube.com/watch?v=BKorP55Aqvg
FAQ731-376: Eng-Tips.com Forum Policies forum1529: Translation Assistance for Engineers Entire Forum list http://www.eng-tips.com/forumlist.cfm

RE: Confused with a simple excel/graph issue

Right-click the chart, select Select Data, Delete series 2, Edit series 1.

Change the references to the X & Y values accordingly (easiest way is to DELETE the contents and click on the cells to create the series cell reference)

OK and vola!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Confused with a simple excel/graph issue

3
This is an example of an Excel bug that has been there for ever, and for some reason many people don't even see it as a bug. You want to plot an XY graph (known to Excel as a scatter graph, even when displaying the data as lines), but if Excel sees any hint that the data is actually a "line graph" (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking. Also if the number of columns is equal to or greater than the number of rows it will treat the data series as being in rows rather than columns. The screenshot below illustrates these problems:


The top left graph was generated by selecting a "Line Chart" as the graph type, with range C4:D6 selected.

Top right had the same selected range, but I selected "Scatter (XY)" as the graph type. It has converted it into a Line Chart, but with the series names converted into sequence numbers (but with values displaying as decimals rather than integers!).

For lower middle I selected range C5:D6 and chose Scatter (XY). This has now plotted as an XY graph, but with the data in rows rather than columns.

For lower right I added an extra row of data and selected C5:D7 and Scatter. The data is now plotted correctly, but it has decided that the text in cell D4 is the graph title. There is actually no need to copy data into the third row. It works the same if the third selected row is blank.

For the lower left chart I selected C5:D6 and chose Scatter (XY), then reselected the data. If you don't select the header rows when you generate the chart it leaves it as an XY graph with one data series, and you can re-select by drag and drop, or through the select data menu (if you prefer).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Confused with a simple excel/graph issue

Thanks, Doug.  I have frequently observed the occasional weird behaviour when creating XY Plots, but had never noted any "rules" determining when it might or might not rear its ugly head.  It all makes (a bit) more sense now.  I shall upgrade my mental view of this behaviour from "occasional quirk" to "definite bug".

RE: Confused with a simple excel/graph issue

that is good info, Doug. I'll add one more thing I have noticed

> if Excel sees any hint that the data is actually a "line graph" (i.e. the X values are not plotted to scale, but with equal spacing for each point), it will convert it to a line graph for you without asking

another thing that can cause this is when the values in the first column (x axis values) are formatted in such a way that excel is not sure whether they are numbers or text. sometimes this happens for imported data.

you can correct it if you know the intricate rules of number formatting, but sometimes I give up and use a workaround to make excel reformat it for me. I do this by building a copy of the data from the original using +0 on each cell. that forces excel to format it as a number without changing the value. the new block of data's will plot x-y fine. if you are have only values (no formulas), then you can copy/paste-special-values from that new block of data to overwrite your original data to reduce the clutter.

=====================================
(2B)+(2B)' ?

RE: Confused with a simple excel/graph issue

"i.e. the X values are not plotted to scale, but with equal spacing for each point"

That's called a Category Axis.

Right click that axis and you can usually select a numeric option rather than text.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Confused with a simple excel/graph issue

Quote (Skip Vought)

"i.e. the X values are not plotted to scale, but with equal spacing for each point"

That's called a Category Axis.

Right click that axis and you can usually select a numeric option rather than text.

The point is that when you include the headers in the selection the x-axis is automatically switched to a "category axis", even though that is not appropriate for an XY graph, and the numbers are already in numeric format, not text.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Confused with a simple excel/graph issue

Sometimes, aren't the old hand-drawn graphs the best? And with soul.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

eBook - Integrating the Engineering Ecosystem
Aras Innovator provides multiple options for integrating data between systems, depending on the scenario. Utilizing the right approach to meet specific business requirements is vital. These needs range from authoring tools, federating data from various and dissimilar databases, and triggering processes and workflows. Download Now
Research Report - Simulation-Driven Design for SOLIDWORKS Users
In this engineering.com research report, we discuss the rising role of simulation and the paradigm shift commonly called the democratization of simulation. In particular, we focus on how SOLIDWORKS users can take advantage of simulation-driven design through two analysis tools: SOLIDWORKS Simulation and 3DEXPERIENCE WORKS. Download Now
White Paper - Industry 4.0 and the Future of Engineering Education
With industries becoming more automated, more tech-driven and more complex, engineers need to keep their skills and knowledge up to date in order to stay on top of this wave—and to be prepared for the Industry 4.0 future. The University of Cincinnati offers two online Master of Engineering degree programs designed specifically for practicing engineers. Download Now
eBook - The Design Gridlock Manifesto
In this eBook, you’ll learn 6 ways old CAD technology slows your company down and hear how design teams have put those problems to rest. “The Design Gridlock Manifesto” shares first-hand modern CAD experiences from 15 companies around the world. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close