Average formula that starts with the first non-zero value, but includes all zeros after the first

Question

First post here so hopefully I do this right. I have a range of values that correspond to monthly sales. I want to take an average of the range starting in the first month sales exist, but I need to include any values that are zero after the first month as well.

I was using =sum(A1:G1)/(countif(A1:G1,"<>0")) but this will exclude all zeros and skew my average.

Thank you

Answer 1

=AVERAGE(INDEX(A1:G1,AGGREGATE(15,6,COLUMN(A1:G1)/(A1:G1<>0),1)):G1)

INDEX(A1:G1,AGGREGATE(15,6,COLUMN(A1:G1)/(A1:G1<>0),1)) returns the first cell that is not 0 and the G1 sets the end.

This will throw an error if no non zero entries are found so you many want to wrap it in an IFERROR:

=IFERROR(AVERAGE(INDEX(A1:G1,AGGREGATE(15,6,COLUMN(A1:G1)/(A1:G1<>0),1)):G1),0)

Now if all the entries are 0 then it will return 0.