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
Get FREE Advanced Excel Exercises with Solutions!
