# Formula to retrieve YTD Average value from any chosen date

1. ## 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. ## Re: Formula to retrieve YTD Average value from any chosen date

This is an example sheet:

3. ## 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. ## 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. ## 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.

There are currently 1 users browsing this thread. (0 members and 1 guests)