How to Calculate YTD (Year-To-Date) Average in Excel (4 Approaches)

Year-To-Date (YTD) refers to the amount of time from the start of the present calendar year or administrative year to the present day. Microsoft Excel offers a variety of options for calculating YTD (Year-To-Date) based on various parameters. In this article, we will use different formulas to calculate YTD (Year-To-Date) Average in Excel.


Download Practice Workbook

You can download the workbook and practice with them.


What Is YTD (Year-To-Date)?

The term “Year-To-Date” refers to the time span between the start of the fiscal year and the present day. YTD is a metric that shows how much money has changed hands over the course of a year.

The YTD notion is useful in a variety of domains, including accounting, finance, and data analysis. It is commonly used by professional accountants and investors. In financial reporting, the YTD number is frequently used. Investors often use the YTD idea to compare and assess portfolio performance.

The amount of profit or loss realized by an investment from the first market day of the new calendar year is referred to as the YTD. Investors and analysts frequently use YTD calculations to evaluate the performance of a portfolio or to compare the recent performance of a number of equities.

When someone uses the term YTD to refer to a calendar year, they are referring to the time between January 1 of the current year and the present date. When they say YTD for a fiscal year, they’re referring to the time between the first day of the fiscal year and the present date.


4 Suitable Approaches to Calculate YTD (Year-to-Date) Average in Excel

In Microsoft Excel, we can use many functions to calculate the YTD average. Let’s demonstrate different approaches with different functions to calculate the YTD (Year-To-Date) average in Excel.

1. Calculate YTD Average Using Excel AVERAGE Function

We can use the AVERAGE function to calculate the YTD average in excel. The AVERAGE function is part of the Excel Statistical Functions category. In Excel, it will produce the mean amount of a specified set of integers. In Excel, it is used to determine the arithmetic mean of a set of parameters.

For calculating the year-to-date average, we are going to use the following dataset. The dataset contains some dates in column B, the total number of sales within those dates in column C and we want to calculate the average of year-to-date sales in column D.

Calculate YTD Average Using Excel AVERAGE Function

For this, we need to follow some procedures to calculate the YTD average sales. Let’s look at the steps down.

STEPS:

  • Firstly, select the cell where you want to put the formula to calculate the YTD average. So, we select cell D5.
  • Secondly, enter the below formula into that selected cell.
=AVERAGE($C$5,C5)
  • Thirdly, press Enter.

Calculate YTD Average Using Excel AVERAGE Function

  • Now, you will be able to see the result in the selected cell and the formula will appear in the formula bar.
  • Further, to copy the formula over the range, drag the Fill Handle down. Or, double click on the Plus (+) sign to AutoFill the range.

Calculate YTD Average Using Excel AVERAGE Function

  • And, that’s it! Finally, we can see the average year-to-date sales in column D.

Calculate YTD Average Using Excel AVERAGE Function

🔎 How Does the Formula Work?

The formula will calculate the YTD average, the dollar sign ($) refers to an absolute reference. By using the cell references as absolute reference ‘$C$5’ we just fixed the cell. This cell will be constant now as this is the first sale on those dates.

Read More: How to Calculate YTD (Year-to-Date) in Excel [8 simple ways]


2. Apply SUM Function to Calculate YTD Average

We can also use the SUM function to calculate the YTD average in excel. The SUM function is used to sum values together. This returns the total of the input values. To calculate the YTD average using the SUM function, we are going to use the following dataset of a super shop. And consists of some dates in column B, the quantity of the sold product in column C, the total price for those products on that particular date in column D and we want to calculate the YTD average of the products for the given dates in column E.

Let’s look at the procedures down to calculate the YTD average using the SUM function.

STEPS:

  • To begin, choose the cell where you want the YTD average formula to be entered. As a result, we choose cell E5.
  • Second, type the formula below into the selected cell.
=SUM($D$5:D5)/SUM($C$5:C5)
  • Finally, hit the Enter key.

Apply SUM Function to Calculate YTD Average

  • You’ll now be able to analyze the data in the selected cell, as well as the formula in the formula bar.
  • Now, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.

Apply SUM Function to Calculate YTD Average

  • That’s all there is to it! Finally, in column E, we can observe the average year-to-date of the products for the given month.

🔎 How Does the Formula Work?

The division between two consecutive values for price and quantity is the focus of this formula. The divided half of this equation is the sum of the product prices, whereas the divisor component is the sum of the product quantities using the SUM function.

Read More: How to Calculate MTD (Month to Date) in Excel (3 Methods)


3. Use SUMPRODUCT Function to Calculate YTD Average in Excel

The sum of the products of related ranges or arrays is returned by the SUMPRODUCT function. We can also use the SUMPRODUCT function to calculate the YTD average in excel. For this, we are using a similar dataset as shown in the second method. Let’s follow the steps down to calculate the YTD average using the SUMPRODUCT function.

STEPS:

  • In the beginning, similarly as before, to calculate the YTD average,  select the cell where the formula will be entered. As a result, we choose cell E5 as our selection.
  • Then, in that selected cell, type in the formula below.
=SUMPRODUCT($D$5:D5)/SUMPRODUCT($C$5:C5)
  • Finally, hit the Enter key to complete the process.

  • The result will now display in the selected cell, along with the formula in the formula bar.
  • Further, drag the Fill Handle down to duplicate the formula across the range. Alternatively, to AutoFill the range, double-click the Plus (+) symbol.

  • That’s everything for now! Last but not least, column E shows the average year-to-date.

Read More: How to Calculate Year over Year Growth with Formula in Excel (2 Methods)


4. Merging SUM and COUNTIF Functions to Calculate YTD Average

We can combine the SUM function and the COUNTIF function to calculate the YTD average in excel. The COUNTIF function is a statistical function that may be used to check the number of cells that match a set of criteria. For this, we are using the dataset below, which contains some dates in column B, and the weekly income in column C, and in column D we will get you the YTD average excluding zero values.

Let’s follow the steps below.

STEPS:

  • Firstly, to compute the YTD average, choose the cell into which the formula will be typed. As a consequence, we have decided to go with cell D5.
  • Secondly, type the formula below into the selected cell.
=SUM(C5:C10)/COUNTIF(C5:C10,"<>0")
  • Thirdly, press the Enter key to finish the procedure.

  • The result, as well as the formula in the formula bar, will now appear in the selected cell.
  • After that, to copy the formula across the range, drag the Fill Handle down. Alternatively, double-click the Plus (+) sign to AutoFill the range.

  • And, for the time being, that’s all there is to it! Finally, column D displays the average year-to-date.

🔎 How Does the Formula Work?

Here, the SUM function will sum the values in the range C5:C10. And we divide the total sum for each cell value with the COUNTIF function, which will count the number of cells excluding zero values.

Read More: Excel Year to Date Sum Based on Month (3 Easy Ways)


Conclusion

The above methods will assist you to Calculate YTD (Year-to-Date) Average in Excel. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!


Related Articles

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo