+ Reply to Thread
Results 1 to 2 of 2

Formula for cyclical data

Hybrid View

  1. #1
    Registered User
    Join Date
    01-02-2012
    Location
    Canterbury, New Zealand
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    2

    Formula for cyclical data

    Hello! This is my first time posting here, so please bear with me.

    My spreadsheet looks like this:
    Column A: Regularly sampled data (cyclical, goes from minima to maxima in about 15 days and then back to a minima in another 15 days)
    Column B: Time of occurrence for each row of column A, increasing one hour every row and continuing for about 1.5 years.

    Column C: Maxima and minima extracted from column A
    Column D: Time of occurrence for each maxima and minima
    Column E: Number of hours between maxima and minima
    Column F: Pi/Column E, Number of radians per hour for each maxima/minima cycle

    Column G: Uses INDEX(C:F, MATCH(B2, D2:D53, 1), 4) to find radians per hour for each row of column B
    Column H: Finds number of hours since the start of a cycle for every row of column B, resetting back to zero after each maxima and minima

    I then want to assign each data point in Column A an angle in radians. If I do this by multiplying G*H, then each cycle in Column A starts at the minima with 0 radians, climbs to maxima where it reaches Pi, and then drops back to 0 radians at the start of the next cycle. I want it to start at 0 radians, climb to Pi, and decrease from Pi back to 0 radians, and then back up to Pi and so on. I can't for the life of my figure out how to get excel to do this!

    Thanks in advance for any help!

  2. #2
    Registered User
    Join Date
    01-02-2012
    Location
    Canterbury, New Zealand
    MS-Off Ver
    Excel 2008 for Mac
    Posts
    2

    Re: Formula for cyclical data

    Here is a sample spreadsheet that might help. Column I is the problem, I want it to go from 0 radians to pi and then back down to 0 radians - not 0 radians to pi and then 0 radians to pi again. The simple solution is to manually change it (as I've done in Column J), but this is not ideal as my actual data has thousands of cycles.
    Attached Files Attached Files

+ 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