Excel Formula Increases Every 3 Years Based on Current Year
In this article, we will discuss how to create an Excel formula that increases every 3 years based on the current year. This can be useful in various scenarios, such as tracking salary increases, inflation rates, or any other data that changes at regular intervals.
Context
Excel is a powerful spreadsheet program that allows users to perform complex calculations and data analysis. One of the key features of Excel is the ability to create formulas that can automate repetitive tasks and calculations. In this article, we will focus on creating a formula that increases every 3 years based on the current year.
Key Concepts
To create a formula that increases every 3 years based on the current year, we will use the following key concepts:
- The YEAR function, which returns the year of a given date
- The MOD function, which returns the remainder of a division operation
- The IF function, which allows us to perform different actions based on a given condition
Creating the Formula
Let's say we want to create a formula that increases by $100 every 3 years based on the current year. Here's how we can do it:
=A1 + IF(MOD(YEAR(TODAY())-YEAR(A1),3)=0,100,0)
Where A1 is the starting value, TODAY() returns the current date, and MOD(YEAR(TODAY())-YEAR(A1),3) returns the remainder of the division of the number of years between the current year and the starting year by 3. If the remainder is 0, it means that 3 years have passed since the starting year, and we increase the value by $100. If the remainder is not 0, we keep the value the same.
Example
Let's say we want to track the salary of an employee who gets a $100 increase every 3 years. We can use the following formula:
=B1 + IF(MOD(YEAR(TODAY())-B1,3)=0,100,0)
Where B1 is the starting salary. If the employee got a raise in the current year, the formula will increase the salary by $100. If not, the formula will keep the salary the same.
In this article, we discussed how to create an Excel formula that increases every 3 years based on the current year. We covered the key concepts and provided an example of how to use the formula in practice. By using the YEAR, MOD, and IF functions, we can automate repetitive tasks and calculations in Excel.