Share via

Calculating previous X months

Anonymous
2023-09-29T19:08:29+00:00

Hi everyone:

We are billed for hundreds of phone lines and I'm trying to keep a running average of usage during the previous 3 months. The monthly bill/usage for the lines are kept in separate tabs by month. If we see no usage over a 3-month period, we will be collecting the hardware &/or disconnecting the lines. Our fiscal year starts in July & I don't need to keep the data longer than one year. So next March, for example, I'll simply delete/overwrite the March statement information in that month's tab with the new data and change the date in Column 'N'. Below is some random usage data pulled from the corresponding monthly statement sheets. What formula can I use to get column 'E' to calculate the average of the previous X months based on today's date? The top row is in date format already.

E F G H I J K L M N O P Q
Avg last 3 months usage Jul 2023 Aug 2023 Sep 2023 Oct 2023 Nov 2022 Dec 2022 Jan 2023 Feb 2023 Mar 2023 Apr 2023 May 2023 Jun 2023
1038235.3410 1548179.3540 1470577.9700 0.0000 0.0000 0.0000 0.0000 0.0000 1177675.0000 745700.3520 571937.0752 1112246.5790
117994.0864 300066.7136 31674.5700 0.0000 0.0000 0.0000 0.0000 0.0000 696010.3000 735625.5232 626945.1264 558708.6336
322260.2752 438469.6320 757824.4100 0.0000 0.0000 0.0000 0.0000 0.0000 289779.9000 522973.3888 621980.9792 262913.9456
69218.5088 166251.1104 184037.4800 0.0000 0.0000 0.0000 0.0000 0.0000 755320.7000 661981.7984 250734.6944 308665.0368
Microsoft 365 and Office | Excel | Other | 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

  1. Anonymous
    2023-09-29T20:28:21+00:00

    Hi RoPark

    I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

    You can use the AVERAGEIFS function. In your case, assuming that you want to calculate the average of the previous 3 months in column E, and that your data starts from row 2, you can use this formula in cell E2 and copy it down:

    = AVERAGEIFS (F2:Q2, F1:Q1, ">=" & EDATE (TODAY (), -3), F1:Q1, "<" & TODAY ())

    This formula will average the values in row 2 from columns F to Q, only if the dates in row 1 from columns F to Q are within the last 3 months from today.

    I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

    Best Regards,

    AnnaThomas

    Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

    3 people found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-29T22:13:29+00:00

    Thank you AnnaThomas! This works perfectly!

    0 comments