dynamic average formula

[daveyc18]

current formula

=AVERAGE(Z39:AG39)

this is because column Z starts with "P1 2024" and column AG has "p8 2024"

this will eventually go to column AK to "P12 2024"

is there a way to make this formula dynamic where it always look for P1 XXXX (XXXX being the current year) then ends in the last non-blank column

so the formula is not only dyanmic in picking up the last non-blank column, but also dynamic in terms of the year (ie when it becomes 2025, I dont need to adjust the starting point of the formula to point to the column with "P1 2025")

 

[offthelip]

try this formula:

=AVERAGE(OFFSET(A39,0,MATCH("P1 " & YEAR(TODAY()),$A$1:$BK$1)-1,1,MONTH(TODAY())))

You can extend the ragne A1 to BK1 to any size you like.
I have assumed that the 8 in the P8 2024 title refers to the month, so that I can find the offset from the start of the average but just finding the width of the range in the offset by using the month number