My goal is to calculate the average of every 9th row starting from D3:

example with delivery revenue in column E

What you see in this Excel sheet is the revenue earned per day of the week, split by weeks. Column A is the date of the day. Column B is the week nr. Column C is the day name. Column D is the revenue earned. Column E is the revenue earned with delivery services.

In the cells F3:F9 and G3:G9 I want to calculate the average revenue of a single day of the week seperately (i.e. all Mondays together, all Tuesdays together, etc.). Until now I used a very basic formula =AVERAGE(D4,D13,D22,D31,D40,D49,D58,D67,D76,D85) Now the datasheet gets larger and larger it gets very inefficient to use this manual method. Therefore I am looking for a formula that can calculate the average value of every 9th row in Column D starting from D3.

Until now I have read this example formula : =AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)) which I don't understand fully and therefore cannot get the desired result.

The following formula =AVERAGE(IF(MOD(tbl[ID], N)=0,tbl[Value]))is great and I understand the strategy behind it however I am not able to apply this in my data set.

CodePudding user response：

use:

=AVERAGEIF(C:C, "Monday", D:D)

or:

=AVERAGE(IFERROR(FILTER(C:C, D:D="Monday")))

CodePudding user response：

without hardcoding the day names into the formulas, if the first block starts in cell A3, and you format your day names in column C as TEXT (maybe using formula like this: =TEXT(A3,"dddd")), then in cell G3 you can put formula like this:

=IFERROR(AVERAGEIF($C:$C,$C3,D:D),0)

then drag down to row 9