Ignore blank cells in Excel INDEX formula

I'm using an Excel INDEX formula (below) to pull data from the last 4 columns in a range and average them, but I need to be able to add columns to my data set later without adjusting the formula, meaning I set the range much bigger (D3:CY3) than I currently have data in (D3:AA3). That way when someone in my office adds data to cell AB3, the last 4 columns in the formula will be Y, Z, AA, AB instead of X, Y, Z, AA. This means I need the formula to ignore (not count as part of the last 4 columns) any that are blank. Can this be done? Thanks!

=AVERAGE(INDEX('2022'!D3:CY3,0,COLUMNS('2022'!D3:CY3)-(4-1)):INDEX('2022'!D3:CY3,0,COLUMNS('2022'!D3:CY3)))

Originally posted on stackoverflow.com with no responses. Attempted to post link to it here as instructed, but new members are not permitted to post links apparently. *shrug*

Hi Rolaids,

I think you can use a CountA function to get the number of non blank cells and use it in an offset function.. See the attached.
Average of Last 4 in a Row using CountA.xlsx

If my example doesn't match you problem then give us a sample workbook showing what you have.

Attached sample