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)

example of the sheet

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


1 Answer 1


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

        not(len(join("", row))), 
            transpose( { row; column(row) } ), 
            "select Col1 
             where Col1 is not null 
             order by Col2 desc 
             limit 6", 0 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.