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

CodePudding user response：

Reversing the column order might solve your issue. Put the stats columns first, columns A-E, followed by the months, most recent first. You could then average columns G-L for the Last 6 Months column. Add a new column each month to the left of column G so the last 6 months is always columns G-L.

CodePudding user response：

You can try with this: it selects all the row up to that cell (-1, that's why the OFFSET), reverses the order with that SEQUENCE and selects the 6 last cells that are not empty:

=AVERAGE(QUERY({TRANSPOSE($A3:OFFSET(AP3,0,-1)),SEQUENCE(COLUMN(AP3)-1)},"Select Col1 WHERE Col1 is not null order by Col2 desc Limit 6"))

As an array:

=BYROW($A3:OFFSET(AP3,0,-1),LAMBDA(each,AVERAGE(QUERY({TRANSPOSE(each),SEQUENCE(COLUMNS(each))},"Select Col1 WHERE Col1 is not null order by Col2 desc Limit 6"))))