+ Reply to Thread
Results 1 to 5 of 5

Formula to retrieve YTD Average value from any chosen date

  1. #1
    Registered User
    Join Date
    07-11-2019
    Location
    Stavanger, Norway
    MS-Off Ver
    Office 2010
    Posts
    13

    Smile Formula to retrieve YTD Average value from any chosen date

    Hi,

    I am currently having a problem getting my YTD Average values in my Excel-sheet.


    I want to make a dynamic YTD Average formula, showing the YTD Average value for what ever date I want to look to.

    In my formula, as shown below, I can make the date dynamic, but not my values follow for the specific date range (YTD to for example 11th of August).

    FORMULA: =(SUMIF(Table1[Date];"<="&TODAY();Table1[Values]))/E3

    In Column A I have my dates (the top date are todays date and it follows to an older date when going downwards).
    In Column B I have my values (values corresponding to the date in the same row).

    Regards

  2. #2
    Registered User
    Join Date
    07-11-2019
    Location
    Stavanger, Norway
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Formula to retrieve YTD Average value from any chosen date

    This is an example sheet:
    Attached Files Attached Files

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Formula to retrieve YTD Average value from any chosen date

    That SUMIF will only aggregate values when the corresponding date is less than equal to today, so if you put in some past date that should work. What is in E3?

  4. #4
    Registered User
    Join Date
    07-11-2019
    Location
    Stavanger, Norway
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Formula to retrieve YTD Average value from any chosen date

    E3 is just showing how many days it is between my chosen interval; for example between 1st of January to 11th of August = 222 days. The sum is divided by this to get the average YTD value for each day.

    When I put in 11th of August instead of TODAY() I get 1228,134 instead of 1227,914.

  5. #5
    Registered User
    Join Date
    07-11-2019
    Location
    Stavanger, Norway
    MS-Off Ver
    Office 2010
    Posts
    13

    Re: Formula to retrieve YTD Average value from any chosen date

    I think I got it, thanks Bob! Due to an earlier simplified correction factor the values did not correspond 100 percent.

+ 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] Formula to retrieve dates record based on key date
    By Raehan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2019, 03:39 PM
  2. Replies: 1
    Last Post: 02-23-2018, 06:38 PM
  3. [SOLVED] I want to have the date entered in cell O3 when Completed is chosen
    By bouncingbudha in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2013, 04:06 PM
  4. [SOLVED] Test whether chosen dates is after today's date
    By h0ps in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2012, 09:28 PM
  5. [SOLVED] Formula to retrieve mutiple values less than value in specific cell, but in date order.
    By rocksan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-18-2012, 11:33 PM
  6. [SOLVED] Formula to find a date range and date average
    By 1Monkey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2012, 11:51 AM
  7. Replies: 1
    Last Post: 04-18-2011, 06:38 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