Forum Discussion
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 "Weekly Total (Direct)", which is the sum total of direct clinical hours (column B) for 5 rows of individual dates (i.e., a work week). Essentially what I want is to have a formula that goes through all the monthly sheets and gives me a single average value of the weekly total values (Column D) but only up to the current date so the average does not get diluted by the empty premade sheets for the future. I hope this makes sense, thanks!
2 Replies
You do not mention which version of Excel you are using (or on which platform you are using it). If you are using Excel 365, Excel 2024, or Excel for the web, you can see the attached workbook for two possible solutions.
In the future, please attach a sample workbook (after removing sensitive information, of course!) to your post, or store it on a sharing service such as OneDrive or Google Drive or DropBox and include a link to it in your post. Forum members are more likely to develop solutions if they do not have to go through the additional work of recreating a workbook from images.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.
