Forum Discussion

RileyRodemaker's avatar
RileyRodemaker
Copper Contributor
Oct 25, 2024

Help with Formula

Hello,   I have a book of spreadsheets where each sheet is a month starting with June 2024. Within each sheet Column A is "Date" consisting of daily dates (e.g., 07/01/2024) and then Column D is "...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Oct 26, 2024

RileyRodemaker 

IF….

Each month's sheet is named as "June 2024", "July 2024", "August 2024", etc.

Column A contains dates, and Column D contains the weekly total values.

The weekly totals are calculated for every 5 rows (workweek) in each month's sheet.

…You want to calculate the average of weekly totals up to today’s date.

 

=AVERAGE(IF('June 2024'!A1:A100 <= TODAY(), 'June 2024'!D1:D100),

    IF('July 2024'!A1:A100 <= TODAY(), 'July 2024'!D1:D100),

    IF('August 2024'!A1:A100 <= TODAY(), 'August 2024'!D1:D100),

    IF('September 2024'!A1:A100 <= TODAY(), 'September 2024'!D1:D100))

 

Make sure to enter the formula using Ctrl+Shift+Enter if you are using an older version of Excel that requires array formulas. This will ensure that the IF conditions are properly evaluated across the ranges.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Resources