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

**Table of Contents**hide

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

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

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

- And, that’s it! 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 [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.

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

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