+ Reply to Thread
Results 1 to 5 of 5

"sliding window", howto move more than one row down for each average, rms

  1. #1
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    "sliding window", howto move more than one row down for each average, rms

    Hi all!

    i would like to calculate the RMS of a sine wave, which i found can be done by this
    Please Login or Register  to view this content.
    my problem is: to calculate the rms-values for one period T = (lets say 1-10 rows and move T/2 =5rows), a half period down and calculate the rms again for 4095 values.

    like this:

    B1 =SQRT(SUMSQ(A1:A10)/COUNTA(A1:A10))
    B2 =SQRT(SUMSQ(A1+5:A10+5)/COUNTA(A1+5:A10+5)) i want this
    B3 =SQRT(SUMSQ((A1+5)+5:(A10+5)+5)/COUNTA((A1+5)+5:(A10+5)+5)) and this and so on...

    COUNTA() can be set to a constant number i guess.

    like a moving/sliding window with the size of 10 rows, moving down 5 rows each time (half it's size). (i can get it to move down one line/row at the time, "moving average")

    hope you understand me ;)

    thanks!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: "sliding window", howto move more than one row down for each average, rms

    Try

    =SQRT(SUMSQ(OFFSET(A$1:A$10,(ROWS($1:1)-1)*5,0))/COUNTA(OFFSET(A$1:A$10,(ROWS($1:1)-1)*5,0)))

  3. #3
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    Re: "sliding window", howto move more than one row down for each average, rms

    Thanks so much @Jonmo1!

    that wasn't that hard ;)

    it works fine in ms office, but not in libre office or google calc do you know why?

    thanks again!

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: "sliding window", howto move more than one row down for each average, rms

    You're welcome.

    Well, it's either the Offset or Rows function that is not supported in Libre Office or Google Calc.
    Assuming the original formula you posted DOES work in Libre and Google.

  5. #5
    Registered User
    Join Date
    02-10-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    3

    Re: "sliding window", howto move more than one row down for each average, rms

    what?

    now i get this errormessage.

    https://drive.google.com/file/d/0B-n...ew?usp=sharing

    what's wrong? earlier i tried it with office live on the web, it worked but it's so slow to work with.

+ 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. [SOLVED] How can I copy "=Average(A1:A2)" and paste as "=Average(A3:A4)" in next space in column?
    By matt_m_is_me in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-27-2014, 11:02 AM
  2. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  3. VBA - IE - click on "Open" in an internet explorer "file download" window
    By victoire in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-22-2013, 07:08 AM
  4. [SOLVED] Excel 2003 VBA - "Maximizing" Window & "Calculating" Workbook
    By JingleRock in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-25-2006, 12:10 AM
  5. Replies: 5
    Last Post: 03-22-2006, 01:30 PM

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