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

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

• Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with a table that shows your manually entered desired results. Also consider sharing a sample spreadsheet. There is a blank sheet maker that lets you share safely. Commented Dec 19, 2022 at 20:22

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
)
)
)
)
)
)