Dynamic MOD & COLUMNS formula

You said data rows by date, but F41:GJ43 is only three rows by 187 columns. Are the dates actually in the columns F41:GJ41? Are they DAILY DATES?
Are you trying to perform the average on the lower two rows?

---

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to "Post Quick Reply" button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

OK, an example of the formula is in cell F5 of the Analysis sheet.

That particular cell is averaging data from three rows on the DataInput sheet.

The input is done in the top half of DataInput sheet and consolidated in rows 40 - 55.

Currently unable to open excel files (at work), but take a look at using averageifs() for this. You can put the start/end dates intheir own cells and reference them, if needed.

BeginDate =Analysis!$C$17
DateRange1 =OFFSET(DataInput!$A$1,40,0,ROWS(DataInput!$A$41:$A$55),Analysis!$C$18-Analysis!$C$17+1)
EndDate =Analysis!$C$18

Analysis F5, (option one: all days within the specified range):

=IFERROR(AVERAGEIFS(INDEX(DateRange1,0,MATCH(F$4,DataInput!$40:$40,0)),INDEX(DateRange1,0,MATCH(F$4,DataInput!$40:$40,0)),">0",DataInput!$D$41:$D$55,Analysis!$E5),"")

Alternatively F31, (option two:only the weekdays same as the beginning date

=IFERROR(AVERAGEIFS(INDEX(DateRange1,0,MATCH(F$30,DataInput!$40:$40,0)),INDEX(DateRange1,0,MATCH(F$30,DataInput!$40:$40,0)),">0",DataInput!$D$41:$D$55,Analysis!$E31),"")