Rolling 12 month forecast - backwards

yorkam

New Member
Joined
Jun 12, 2014
Messages
8
Help, I'm looking for a formula to calculate a rolling backwards forecast with actuals, based on a date field to calculate a rolling 12 month total. I'd like to automate it based on the current date so should take last twelve months from today.

For example:
Month1 Month 2 Month 3...... to month 12 and then next month add in month 13
100 120 100
So for the first month the total will be the sum of month 1 to 12
next month the value will be month 2 to 13
next month value will be month 3 to 14

I need it to be a formula in a cell and not VBA.
I can do it via a sumif on the basis that someone manually enters it a condition and takes the condition out the next period, but do not want to rely on individuals, so any way to automate would greatly be appreciated.
thanks in advance
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,396
How about:

Book1
ABCDEFGHIJKLMNOPQRSTU
1Date1/1/20202/1/20203/1/20204/1/20205/1/20206/1/20207/1/20208/1/20209/1/202010/1/202011/1/202012/1/20201/1/20212/1/20213/1/20214/1/20215/1/20216/1/20217/1/20218/1/2021
2Value123456789101112131415161718
3
4Last 12 months150
Sheet2
Cell Formulas
RangeFormula
B4B4=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(TODAY(),-12),B1:ZZ1,"<="&EOMONTH(TODAY(),0))
 

Massoud

New Member
Joined
Jun 27, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
How about:

Book1
ABCDEFGHIJKLMNOPQRSTU
1Date1/1/20202/1/20203/1/20204/1/20205/1/20206/1/20207/1/20208/1/20209/1/202010/1/202011/1/202012/1/20201/1/20212/1/20213/1/20214/1/20215/1/20216/1/20217/1/20218/1/2021
2Value123456789101112131415161718
3
4Last 12 months150
Sheet2
Cell Formulas
RangeFormula
B4B4=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(TODAY(),-12),B1:ZZ1,"<="&EOMONTH(TODAY(),0))
Hi Eric
I had the same question and found this on a google search.
Your formula works great.
How do I replace "EOMONTH(TODAY()" with a cell reference instead? Let's say I want the rolling 12 but starting last month going back 12 and not this month. The referenced call will say 4/30/2021
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,396
Welcome to the forum!

Just replace TODAY() with your cell reference. If it's A1, the formula would be:

Excel Formula:
=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(A1,-12),B1:ZZ1,"<="&EOMONTH(A1,0))
 

Massoud

New Member
Joined
Jun 27, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=SUMIFS(B2:ZZ2,B1:ZZ1,">="&EOMONTH(A1,-12),B1:ZZ1,"<="&EOMONTH(A1,0))
Thank you, Eric. It worked and I appreciate the help.
 

CindaH

New Member
Joined
Sep 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
I don't think the formula is giving me the correct answer. Here's my data:

Date01-Jan-2001-Feb-2001-Mar-2001-Apr-2001-May-2001-Jun-2001-Jul-2001-Aug-2001-Sep-2001-Oct-2001-Nov-2001-Dec-2001-Jan-2101-Feb-2101-Mar-2101-Apr-2101-May-2101-Jun-2101-Jul-2101-Aug-2101-Sep-2101-Oct-2101-Nov-2101-Dec-21
Units94.2589.2588.50105.0089.0088.5086.0078.0068.5068.0065.0078.0073.2589.00106.25101.2586.0056.7597.2561.0080.25
Rolling 12 Months962.00962.00962.00962.00962.00962.00962.00962.00962.00962.00894.00829.00751.00677.75588.75482.50381.25295.25238.50141.2580.250.000.000.00

For example, 01-Feb-21 says 677.75, but Feb-20 through Jan-21 should be 977. What am I missing?

Thanks!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,396
Welcome to the MrExcel forum!

Just as an FYI, your question is more likely to be seen and answered if you open a new thread.

I can't tell what's off with your formula, since you didn't say what it is. But consider this:

Book1 (version 1).xlsb
ABCDEFGHIJKLMNOPQRSTUVWXY
1Date1-Jan-201-Feb-201-Mar-201-Apr-201-May-201-Jun-201-Jul-201-Aug-201-Sep-201-Oct-201-Nov-201-Dec-201-Jan-211-Feb-211-Mar-211-Apr-211-May-211-Jun-211-Jul-211-Aug-211-Sep-211-Oct-211-Nov-211-Dec-21
2Units94.2589.2588.51058988.5867868.568657873.2589106.25101.258656.7597.256180.25
3
4Rolling 12 Months998977976.75994.5990.75987.75956967.25950.25962894829751677.75588.75482.5381.25295.25238.5141.2580.25000
5998977976.75994.5990.75987.75956967.25950.25962894829751677.75588.75482.5381.25295.25238.5141.2580.25000
Sheet23
Cell Formulas
RangeFormula
B4:Y4B4=SUMIFS($B$2:$ZZ$2,$B$1:$ZZ$1,">="&B1,$B$1:$ZZ$1,"<="&EOMONTH(B1,11))
B5:Y5B5=SUM(B2:M2)


The B4 formula will sum up anything between the B1 date and the next 11 months. But if your columns are sequential months, like you showed, then you can use the simpler formula in B5.
 

Forum statistics

Threads
1,141,204
Messages
5,704,954
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top