+ Reply to Thread
Results 1 to 4 of 4

Average of last 30 days, updated around today()

  1. #1
    Registered User
    Join Date
    04-29-2013
    Location
    London
    MS-Off Ver
    Excel MAC 2011
    Posts
    16

    Average of last 30 days, updated around today()

    I'm trying to create a column, then a graph, of the % returns over the last 30 days, which would automatically update daily- since I'd like today() to be the starting point.

    Can anyone help, at least with the formula for the column. Ideally, every other cell in the column would be blank, apart from today.

    average percentage returns of last 30 days, variable amount invested, varied returns. Hope thats clear (ish)

    value contributed returns to date
    £1,585,000 1600,000 -0.91%
    £1,585,000 1600,000 -0.89%
    £1,597,000 1600,000 -0.18%
    £1,648,000 1650,000 -0.13%
    £1,648,000 1650,000 -0.13%
    £1,645,000 1650,000 -0.30%
    £1,667,000 1650,000 1.05%
    £1,680,000 1650,000 1.82%
    £1,678,000 1650,000 1.69%
    £1,665,000 1650,000 0.90%
    £1,665,000 1650,000 0.90%
    £1,665,000 1650,000 0.90%

    Thanks so much

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Average of last 30 days, updated around today()

    does the entry include the date?

    if so, match(today()-30,...) and set the graph parameters accordingly...

    although graphs cannot handle blanks very well...

  3. #3
    Registered User
    Join Date
    04-29-2013
    Location
    London
    MS-Off Ver
    Excel MAC 2011
    Posts
    16

    Re: Average of last 30 days, updated around today()

    it does include the date, yes.

    Putting aside the graph, how would I get the last 30 days average returns in a cell next to today's date?

    I'm crash coursing excel, so am a newbie with small patches of advanced knowledge.
    Last edited by Honeyfoot; 04-29-2013 at 10:41 AM.

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Average of last 30 days, updated around today()

    OK.

    set the boundaries of the range to be averaged

    bottom: the row with today's info
    top: the row with today-30 info (or the very next business day)

    =match(today(),a:a,0) in any blank cell say g1
    =match(today()-30,a:a,0) in another cell say h1

    let's say the returns are in column C, so the average will be given by:

    =sum(indirect("c"&g1&":c"&h1))/counta(indirect("c"&g1&":c"&h1))

+ 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