Share via

Excel Formula Help

Anonymous
2025-02-01T22:02:07+00:00

Hi,

I need some help with an excel formula. I have a daily report I am putting together where I need the plan, actual, variance for the week to sum based on what day the report is on run on. I am using =today() to identify what day is today. I am looking to sum and average the values for the days that are less than today for that week.

Here is a screen shot of how the data is arranged:

Looking for a function that if Today-1/30 that it only sums 1/27-1/29 or average 1/27-1/29 for the plan and actual columns.

Thanks.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

HansV 462.6K Reputation points
2025-02-01T22:49:58+00:00

Thanks!

Sum of Plan: =SUM(IF((F$3:R$3>0)*(F$3:R$3<TODAY()), E7:Q7))

Average of Plan: =AVERAGE(IF((F$3:R$3>0)*(F$3:R$3<TODAY()), E7:Q7))

Sum of Actual: =SUM(IF((F$3:R$3>0)*(F$3:R$3<TODAY()), F7:R7))

Average of Actual: =AVERAGE(IF((F$3:R$3>0)*(F$3:R$3<TODAY()), F7:R7))

If you have Excel 2019 or earlier, confirm each of these formulas by pressing Ctrl+Shift+Enter.

Was this answer helpful?

1 person found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-02-01T22:24:08+00:00

    I have edited the original post

    Was this answer helpful?

    0 comments
  2. HansV 462.6K Reputation points
    2025-02-01T22:21:19+00:00

    We cannot see the column letters and row numbers in the screenshot.

    Was this answer helpful?

    0 comments