I am trying to create a tracking sheet for our local high school's target shooting team. My Google sheet will calculate the the average of all rounds shot. However, I am trying to write a formula that will only take the average from the last 10 entries while omitting any cells with a 0. Now Column AB5:AB42 will calculate the average for each row. In cell B3 it will calculate the average for all rounds. I would like for cell B3 to only calculate the last 10 entries while ignoring the zeros on the sheet. Any ideas are greatly appreciated.
I was able to calculate the average of all rounds in B3, but not been able to calculate last 10 entries while omitting zeros
Try this
=average(QUERY(tocol(A2:A,1),"select Col1 where Col1 !=0 offset "&if(COUNTIF(TOCOL(A2:A,1),"<>0")<10,0,COUNTIF(TOCOL(A2:A,1),"<>0")-10)))
Change the column references to your column numbers (listed here as A2:A).