+ Reply to Thread
Results 1 to 8 of 8

Irregular Data Interval Numerical Integration

  1. #1
    Registered User
    Join Date
    08-27-2020
    Location
    Bakersfield, CA
    MS-Off Ver
    Office 365
    Posts
    4

    Irregular Data Interval Numerical Integration

    I have this dataset with ~21,000 points. I need to integrate at ~44,000 regular intervals, but the data is spaced irregularly. Here's an image of a small subset for context:
    Data.png

    The vertical grey lines are the intervals I need to integrate at (1 unit). The circles represent the data points. I'm trying to come up with some sort of algorithm that will allow me generate a new data set. I'm not sure if this is more an Excel programmer question or a math question, so I'm posting this to a math forum as well.

    I've attached a CSV of the points in the above image in case you want to experiment. Any ideas?
    Attached Files Attached Files

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

    Re: Irregular Data Interval Numerical Integration

    What is your desired integration algorithm? Trapezoid rule? Expanding the data from 21000 point to 44000 points means that you must add new points to the data set. How do you intend to add those points? Note that simple linear interpolation should yield the same answer as trapezoid integration using the existing 21000 data points, so it seems like superfluous step to turn 21000 points into 44000 points if you intend to use linear interpolation to create the extra points.

    Whatever the answers to those questions are, the usual spreadsheet programming strategy is to take your column of x,y points, compute dx and use that with the y values to compute the area of each "slice" (exact formula depends on your chosen Riemann sum), then add up the area of all slices at the end (usually with a SUM() function).
    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
    08-27-2020
    Location
    Bakersfield, CA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Irregular Data Interval Numerical Integration

    "What is your desired integration algorithm? Trapezoid rule?"
    I was getting wrapped up in Simpsons rule, but your comments make feel like I was going astray. Also, another point of fact, the domains of dataset I'm looking for is equal to the domain of the original set.


    "Expanding the data from 21000 point to 44000 points means that you must add new points to the data set. How do you intend to add those points? Note that simple linear interpolation should yield the same answer as trapezoid integration using the existing 21000 data points, so it seems like superfluous step to turn 21000 points into 44000 points if you intend to use linear interpolation to create the extra points"
    I am open to suggestions as to methods, so long as the answer is close to target. I already know the integral of the whole dataset--we can use that as a check. Just to clarify, I need the integral of the curve in one unit intervals. Would you care to show me an example of how you would interpolate the attached dataset?


    I'm fairly proficient in Excel, but I'm a master with AutoCAD. I'm devising a way where I might normalize the data there, and reimport to excel, where all my intervals will then align with my data. Not there yet though, so please jump in if you can.

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

    Re: Irregular Data Interval Numerical Integration

    If memory serves, Simpson's rule requires evenly spaced intervals. Is that the only reason you were wanting to expand the data set? To get enough evenly spaced intervals so that you could ultimately use Simpson's rule? Or is there some other reason you are wanting to use Simpson's rule?

    Whenever I see a problem like this, my first inclination is to use a trapezoid rule (which is the average of the left-hand and right-hand sums, so I often include those two estimates as well). Using the trapezoid rule will eliminate the need for the interpolation step and would give a more straightforward path towards estimating the integral.

    It's your project -- what do you think?

  5. #5
    Registered User
    Join Date
    08-27-2020
    Location
    Bakersfield, CA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Irregular Data Interval Numerical Integration

    Memory is likely serving you better than I. I can't tell you why I thought Simpson's rule, just that I did. I need the integral of the data I have at single unit domains. I have another dataset that I need to contrast with this one, and it is already configured such that every unit of data is in tabulated form. I am trying to match that. If I can integrate the data as individual integer domains, each integration corresponding to a single data entry in a list of coordinates, then I will end up with the areas bounded by units on the left and right( e.g 5 and 6), zero on the bottom, and the range of the data on the top, totaling 35,040 separate integrations (not 44k, I was mistaken previously). This corresponds to one year at 15 minute intervals. The sample I attached was for one day.

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Irregular Data Interval Numerical Integration

    Hi guys,

    If you want to transform your current irregular spaced data into regular intervals I just used linear interpolation to find the points on this attached worksheet.

    Just change cell F3 to change the number of points to equally spread your data over. I dragged the formula down to about row 400 or so, so you can do at least 400 points. I also calculated the integral for your original data and the transformed data and of course as your interval gets smaller the two calculations get closer. I overlayed the two charts on top of each other to get a neat diagram too.
    Happy with my advice? Click on the * reputation button below

  7. #7
    Registered User
    Join Date
    08-27-2020
    Location
    Bakersfield, CA
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Irregular Data Interval Numerical Integration

    I wanted to follow up and close out this issue. As I said previously, I'm a master with AutoCAD, so I was able to pick new points at the intervals I needed. Took about an hour, and the best thing is it's immediately visually verifiable, whereas the numerical solutions always left me with a sense of doubt, as I can't "see" the numbers. Thank you for your input concerning this matter.

  8. #8
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Irregular Data Interval Numerical Integration

    Great. Glad it helped

+ 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. Replies: 0
    Last Post: 04-23-2012, 10:06 AM
  2. Excel 2007 : Choosing cell from irregular interval
    By shinizaki in forum Excel General
    Replies: 2
    Last Post: 10-05-2010, 08:39 AM
  3. Numerical Integration
    By 77anders in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-09-2009, 02:04 PM
  4. Integration of a function over some interval
    By jens1234 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-13-2008, 08:25 PM
  5. Numerical integration to a maximum value using VBA
    By John in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-05-2006, 10:45 PM
  6. numerical integration
    By integreat in forum Excel General
    Replies: 4
    Last Post: 05-11-2006, 09:45 PM
  7. Numerical Integration Excel 97
    By inigo45 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2005, 09:36 AM

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