+ Reply to Thread
Results 1 to 4 of 4

How to 'flatten' a set of values

  1. #1
    Registered User
    Join Date
    12-03-2009
    Location
    Rotterdam, Holland
    MS-Off Ver
    Excel 2007
    Posts
    2

    How to 'flatten' a set of values

    Good day! I'm not sure if it's because I can't find the right term for it or because I just can't search. But anyway, I can't find a solution to this either on excelforum.com or on Google.

    Everything I find on Google has something to do with a trendline and even though that's exactly what I want, I need something else. To be more precise, I need the data behind the line, not the visual line itself. Also, the trendline shifts unwanted on the x-axis.

    In this spreadsheet (XLSX) of mine you can find a set of data that came from a datalogger. It logs the speed of a wheel, I gave it quite a yank and then let it spin till it stopped. What I want to reveal is the deceleration and therefore I need the velocity.

    The speed of the wheel is measured by a frequency sensor that measures what the frequency is of holes in a disc that pass by. The disc has 30 holes and therefore, 30 Hz is one rotation per second.

    The nice thing about this is that I can really precisely measure the velocity of the wheel. IF I can actually manage to take away the 'errror' effect of the sensor itself. You see, that's where the problem lies. The sensor has some kind of deviation that makes the output data 'wobble' up and down. This effect is bigger when the measured frequency is bigger.

    I'd like to 'flatten' this data so I get a nice and smooth curve. Because when I do, I get a nice and smooth deceleration graph too! Can someone explain me how to do this, give me an example, a website/tutorial or whatsoever? I'm really struggling here and since this case is hard to Google I just thought I'd put it here.

    The things I've tried are:

    Lineair interpolation, although Excel can't do that, I tried it manually but it's very time consuming when you have over 3000 values.
    =TREND is some kind of interpolation I discovered. Maybe I'm doing it wrong but if I try to apply this I still get a wobbly line.
    =AVERAGE between 10 values. But I feel like this isn't a nice way since it will just create a "staircase" graph.

    Here are some illustrations of the case (Dutch titles, don't worry, the XLSX is English)

    \1

    \1

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to 'flatten' a set of values

    cant you just add a trendline to the graph? then format the data series to no marker?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    12-03-2009
    Location
    Rotterdam, Holland
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: How to 'flatten' a set of values

    As I told in my first post, I would like to have the data, not the trendline itself. So the data is important to me, since I will use it in another piece of software. I simply need to flatten out my data so that I get a clear acceleration.

    The acceleration (the red graph) seems to be very jerky now and that's because the velocity is not nice and smooth. The smoother my velocity data is, the better the acceleration will become visible.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: How to 'flatten' a set of values

    surely changing the data to smoother values is exactly the same as inserting a trend line in a chart?
    not sure of the benifits of smoothing data as i have no idea why you need that in another application
    Last edited by martindwilson; 12-04-2009 at 04:52 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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