+ Reply to Thread
Results 1 to 11 of 11

Exponentially weighted moving average result in a single function\cell

  1. #1
    Registered User
    Join Date
    12-05-2022
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Post Exponentially weighted moving average result in a single function\cell

    Is it possible to calculate an exponentially weighted moving average across a set of data without having to calculate each period separately?

    This is similar to =SUMPRODUCT(array1,array2)/sum(array1) however in my case, I need to take 80% of all of the prior periods result and adding 20% of the current period. There could be 200+ prior periods so a simple weighted average will not work.

    I have tried MMULT, VPV and a number of loan\investment functions already. I may just not know what to search for.

    I have added a worksheet demonstrating the problem.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-12-2022
    Location
    Edmonton AB CANADA
    MS-Off Ver
    2019
    Posts
    25

    Re: Exponentially weighted moving average result in a single function\cell

    Try this pasted into D20 and copied to the right. (be sure to keep the $ sign where it is)

    =AVERAGE($C10:C10)*0.8+0.2*D10

  3. #3
    Registered User
    Join Date
    12-05-2022
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: Exponentially weighted moving average result in a single function\cell

    Thanks for your response. However, that doesn't achieve what I am looking for and it also doesn't take into account the weighting from the previous period's result. Which means the end result is not the same.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exponentially weighted moving average result in a single function\cell

    Do you want rows 10-20 to be deleted and get 3.9 in ONE cell??? or what?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    12-05-2022
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: Exponentially weighted moving average result in a single function\cell

    In a nutshell yes. Just want to see see the data then the EWMA result. I guess I should have said that to begin with.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exponentially weighted moving average result in a single function\cell

    OK. I have no idea HOW to do it, but I do at least now know WHAT is needed. That's a good place to start.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Exponentially weighted moving average result in a single function\cell

    UDF function

    Please Login or Register  to view this content.
    =maverage($C$10:$N$10,$C$3:$C$4)

    Change range to suit
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exponentially weighted moving average result in a single function\cell

    Thanks JT... I was going round and round in circular references!!

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Exponentially weighted moving average result in a single function\cell

    @GK: ... Snap!! so was I trying a formulaic solution so fallback for me is VBA

  10. #10
    Registered User
    Join Date
    06-02-2020
    Location
    Turkey
    MS-Off Ver
    365 TR - V.2309
    Posts
    97

    Re: Exponentially weighted moving average result in a single function\cell

    Hi,

    With Office365, a solution can be obtained with a simple formula
    But, solution with formula in older versions... ???

    Spill range

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Result of last value only

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-05-2022
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    7

    Re: Exponentially weighted moving average result in a single function\cell

    Thanks immensely. Once I understood the mechanics I was able to write the function that I needed.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    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)

Similar Threads

  1. Weighted Average Function for Series Weighted by Increments of 1
    By kratsexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-05-2018, 11:38 AM
  2. [SOLVED] calculate exponentially weighted moving average (EWMA) in one cell without separate column
    By kmaloney01 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-02-2017, 12:36 AM
  3. Weighted Moving Average
    By steven723 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2014, 09:50 PM
  4. Exponentially Weighted Moving Averages
    By controlfreak in forum Excel General
    Replies: 0
    Last Post: 01-09-2014, 12:12 PM
  5. [SOLVED] Calculate a weighted average in a single cell based on multiple criteria
    By _Bryan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-14-2013, 04:38 PM
  6. [SOLVED] Weighted moving average
    By Boom1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-01-2006, 05:30 PM
  7. [SOLVED] Re: Weighted moving average
    By Boom1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-01-2006, 03:55 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