How can one calculate the average of last X values in a row, but ignoring a set number of cells

Question

I've been trying hard to solve this but haven't managed yet.

I have this sheet I'm using to keep track of my expenses on a monthly basis and every month I create a new column to register different types of expenses from that month. On each line, I have different expenses like groceries, entertainment, car, etc, and every month I add new values. The heather for each column has the month it corresponds to.

At the end of each line, after the last inserted monthly data, I also have some columns which I use to make calculations, such as total average and total value, so that I know how much I spend on each category. What I would like to do is to have another cell, at the end of each line, calculating the last 6 months average for each category (meaning, for each line)

As you can see in the image, every thin to the right of column AO is statistics. In column AR I would like to have a formula to calculate the last 6 month average, meaning it would look to the 6 months to the left of column AP (any cell with values) and do the average of those.

I would like this do be done without hardcoding "AP" column, since that changes every time I create the column for a new month. There is no problem though in hardcoding the number of statistics columns that are found at the end of each line (which are 5 in this example).

Any help is deeply appreciated

Answer 1

One way to do that is to use byrow() in cell AR3, like this:

=arrayformula( 
  byrow( 
    A3:AO, 
    lambda( 
      row, 
      if( 
        not(len(join("", row))), 
        iferror(1/0), 
        lambda( 
          lastSixMonths, 
          average(lastSixMonths) 
        )( 
          query( 
            transpose( { row; column(row) } ), 
            "select Col1 
             where Col1 is not null 
             order by Col2 desc 
             limit 6", 0 
          ) 
        ) 
      ) 
    ) 
  ) 
)