0

I have 2 sets of data in tabular form, say (x,y) and (x,z). Both sets share variable x, but for each set x has a different increment (see example). I would like to generate a graph (y,z) without using VBA or third party interpolation apps. Thank you in advance for your responses.

Example:

Dataset 1:

x: 1, 2, 3

y: 1, 2, 7

Dataset 2:

x: 0.3, 0.6, 0.9, 1.2, 1.5, 1.8, 2.1, 2.4, 2.7, 3.0

z: 1,   2,   3,   4,   5,   6,   7,   8,   9,   10
1
  • Just to clarify, are you looking to graph Y and Z in relation to X, as described in Máté Juhász's answer, or Y vs. Z (without X, where one would become the X and the other the Y for graphing purposes)?
    – fixer1234
    Sep 5, 2016 at 17:02

2 Answers 2

1

You can easily do it with scatter plot:

  • Select your first dataset (both x and y values)
  • navigate to insert - charts - XY (Scatter)
    (this can be slightly different depending on your Excel version)
  • insert the chart
  • right click on the chart - select data - add new series
    • here select data for horizontal and vertical axis from the second dataset

enter image description here

1
  • +1 for figuring out what the question was asking.
    – fixer1234
    Sep 5, 2016 at 16:55
0

I'm assuming you want to graph Y vs. Z (without X, where one would become the X and the other the Y for graphing purposes. I had to do this just now.

  1. I made sure both sets are sorted on the x value, in separate sheets.
  2. I took the larger data set (your data set 2).
  3. In the third column (the column after your z), top cell, I wrote this formula: =arrayformula(vlookup($a$1:$a$10,'other_sheet'!$a$1:$a$3,2,true). Call this column y.
  4. I created a chart from columns Z and Y. You may have to switch Z and Y to get the correct series on the X axis.

Notes:

  • 'other_sheet' has the smaller data set (your set 1). I don't know what's more efficient. I'm assuming that a larger number of VLOOKUPs on a smaller data set is more efficient than a smaller number of lookups on a larger data set.
  • I'm starting both data sets from $a$1,assuming no header rows; you can adjust that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.