+ Reply to Thread
Results 1 to 3 of 3

plot different datasets with same horizontal axis in 1 plot

  1. #1
    Registered User
    Join Date
    05-15-2021
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    4

    plot different datasets with same horizontal axis in 1 plot

    Hi,

    My problem seems quite easy, but I am not able to solve it quickly. I am measuring two different quantities of an object, each with a different measuring device. The first device is continualy measuring and producing an average value every 30 seconds. The second device is measuring every four seconds for a minute. After that this device has te recalibrate itself and is unable to measure the next minute. After that periode it start again with measurements every four seconds.

    I do have two different datasets with an equal horizontal axis (time) but different values. Both quantities of the object (its behaviour in time) have to be plotted in 1 plot.

    Is it possible to do this in an easy way instead of manually introducing a lot of blank cells to line up the different arrays?
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,745

    Re: plot different datasets with same horizontal axis in 1 plot

    It looks like it should be simple lookup function(s).

    M4 =VLOOKUP($L4,$H$3:$I$38,2,FALSE)
    N4 =VLOOKUP($L4,$A$3:$E$19,2,FALSE)
    O4 =VLOOKUP($L4,$A$3:$E$19,3,FALSE)
    P4 =VLOOKUP($L4,$A$3:$E$19,4,FALSE)
    Q4 =VLOOKUP($L4,$A$3:$E$19,5,FALSE)

    This will return N/A where the entries don't exist in a given table, and the corresponding value where it does. Before deciding what to do with the N/A error, read this (https://peltiertech.com/mind-the-gap...g-empty-cells/ ) on the way to deciding what to do with the error values.

    If you have access to the XLOOKUP() function, you may look into that, as MSFT claims that XLOOKUP() is faster for these exact match lookups. You might also consider using the approximate match binary search option (4th argument of VLOOKUP() is TRUE) as that is faster. You might also consider an INDEX() and MATCH() combination that allows you to perform the slow lookup step once per row, that will further improve performance.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    05-15-2021
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    4

    Re: plot different datasets with same horizontal axis in 1 plot

    Thanks a lot,

    It does the trick. I tried VLOOKUP, but made a mistake as I see now.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Shifting horizontal axis labels on a Scatter Plot
    By OmicronCoder in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-29-2020, 11:29 AM
  2. Replies: 5
    Last Post: 05-03-2020, 06:01 AM
  3. Adding horizontal (category) axis labels on an X-Y scatter plot
    By chetanchimate in forum Excel General
    Replies: 6
    Last Post: 01-01-2016, 11:40 PM
  4. Plot events along horizontal axis
    By jamie2v in forum Excel General
    Replies: 11
    Last Post: 02-27-2015, 01:46 AM
  5. Replies: 2
    Last Post: 11-27-2012, 01:25 PM
  6. Horizontal axis limiting formula in xy scatter plot
    By S K ROY in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-06-2011, 09:15 AM
  7. How to plot scatter graph on horizontal axis
    By alsupport in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-28-2010, 04:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1