I am attempting to calculate the yearly difference as a percentage for month to date sales and gross profit dollars. The formula I am using for previous months is as follows
=Average(H8:H31)/Average('2023'!H8:H31)-1 & =Sum(H8:H31)/Sum('2023'!H8:H31)
Is there a way to adapt this formula for the current month so that it auto updates based on the number of filled columns in the 2024 worksheet? Picture examples of the data I'm working with are shown below.
2023
2024
The results of my formulas returns the following (Displays the difference between 2024 & 2023/2022/2021)
I essentially want a formula that calculates the average and sum based off of the number of days that have currently passed in the current month, without the need to manually update the formula.
Any suggestions or ideas would be greatly appreciated.
*Note* I know the easy option is to just update the cell references as each day passes, but I would like for this to be automatic.
=Average(H8:H31)/Average('2023'!H8:H31)-1 & =Sum(H8:H31)/Sum('2023'!H8:H31)
Is there a way to adapt this formula for the current month so that it auto updates based on the number of filled columns in the 2024 worksheet? Picture examples of the data I'm working with are shown below.
2023
2024
The results of my formulas returns the following (Displays the difference between 2024 & 2023/2022/2021)
I essentially want a formula that calculates the average and sum based off of the number of days that have currently passed in the current month, without the need to manually update the formula.
Any suggestions or ideas would be greatly appreciated.
*Note* I know the easy option is to just update the cell references as each day passes, but I would like for this to be automatic.
