Average of range between two specified cells

Hi,

I'd like to have Excel take the average of the "total no. of cakes" and "no. of cakes w. mistakes", respectively, for the last twelve months of data. I could just do this manually by writing "=AVERAGE(B3:B14)" in F5 and "=AVERAGE(C3:C14)" in F8, respectively. However, I'd like to minimize the amount of manually writing in information, so that I only have to insert new data (next time 16-05) and write in the current month and the same month last year.

So my question is: Is there a way to have Excel take the average of the cells in row B below row A containing the value from F2 and above row A containing the value from F3?

Best regards
Kasper

Hi
Welcome to the forum.

I am not very sure what are your requirements but try if this is what you need.

For column B average use-

Please Login or Register  to view this content.

For column C average use-

Please Login or Register  to view this content.

Also, as you are new to the forum I would like to tell you that we can upload workbooks at this forum which provide better flexibility than attaching images.
Check out the link in my signature below to know how you can attach workbooks at this forum.

How about

Total cakes
=SUMPRODUCT(--(F2>=A2:A16)*(F3<=A2:A16)*(B2:B16))/COUNTIFS(A2:A16,"<="&F2,A2:A16,">="&F3)

Total cakes with mistakes
=SUMPRODUCT(--(F2>=A2:A16)*(F3<=A2:A16)*(C2:C16))/COUNTIFS(A2:A16,"<="&F2,A2:A16,">="&F3)

If the month and year is in the correct format (not text) then you can also use this-
=AVERAGEIFS(B:B,A:A,"<="&F2,A:A,">="&F3)

or can use F5

Please Login or Register  to view this content.

or can try F5

Please Login or Register  to view this content.