Forum Discussion

bbilodeau's avatar
bbilodeau
Copper Contributor
Apr 22, 2024

Formula needed to calculate average over current day in year

I'm looking for a formula to calculate a year-to-date daily average using Excel that would automatically change the amount of days as time goes on. Is there a function that can use a YTD sales quantity and divide by the current number of days from the beginning of the current year?

 

For example, say I have a total of 45,660 parts sold YTD as of April 22 2024. The average daily sales of that would be 45,660/112=407.7. Would I be able to use a function that would automatically change the 112 value to 113 as the calendar day advances?

excel
Formulas and Functions

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 22, 2024

    bbilodeau 

    The number of days in the current year up to and including today is

     

    TODAY()-DATE(YEAR(TODAY())-1, 12, 31)

     

    So you can use

     

    =YTD_Quantity/(TODAY()-DATE(YEAR(TODAY())-1, 12, 31))

    • Cookster's avatar
      Cookster
      Copper Contributor
      May 06, 2024

      HansVogelaar 

      Good Morning

      The question asked regarding the YTD worked perfectly. How can this be done as a MTD format?

      Thanks 

      Neil

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 07, 2024

        Cookster 

        The number of days in the current month up to and including today is

         

        TODAY()-EOMONTH(TODAY(), -1)

         

        So you can use

         

        =MTD_Quantity/(TODAY()-EOMONTH(TODAY(), -1))

         

        This assumes that you have a named range MTD_Quantity.

Resources