**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.

**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.

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

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**.

**STEPS:**

- Select the cell where you want to put the formula to calculate the
*YTD*average. So, we select cell**D5**. - Enter the below formula into that selected cell.

`=AVERAGE($C$5,C5)`

- Next, press
**Enter**.

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

- Finally, we can see the average year-to-date sales in column
**D**.

🔎 **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

**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 supershop. It 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**.

**STEPS:**

- Choose the cell where you want the
*YTD*average formula to be entered. As a result, we selected cell**E5**. - Then, type the formula below into the selected cell.

`=SUM($D$5:D5)/SUM($C$5:C5)`

- Finally, hit the
**Enter**key.

- You’ll 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.

- 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 dividend 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. 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.

**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 selected cell**E5**. - 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 be displayed 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 YTD (Year to Date) in Excel

**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.

**STEPS:**

- To compute the
*YTD*average, choose the cell into which the formula will be typed. We have decided to go with cell**D5**. - Then, type the formula below into the selected cell.

`=SUM(C5:C10)/COUNTIF(C5:C10,"<>0")`

- Press the
**Enter**key to finish the procedure.

- The result will appear in the selected cell.
- To copy the formula across the range, drag the
**Fill Handle**down. Alternatively, double-click the*plus (+)*sign to**AutoFill**the range.

- 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**. 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

**Download Practice Workbook**

You can download the workbook and practice with them.

**Conclusion**

The above methods will assist you to calculate the *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 on our website.

**<< Go Back to YTD Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel**