+ 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
    11

    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
    11

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

    This is an example sheet:
    Attached Files Attached Files

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

    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
    11

    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
    11

    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)

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