headcount forecast formula 3 month window

[lefty38]

I am looking for a way to calculate a headcount forecast
i have posted an example
even though the example is built in Excel 2007 - I need the formula for excel 2003 (Our company has not upgraded to 07 - if that makes a difference)
in June 2011 the current month
we know accurately the actual headcount so in G7 the formula is 100% (this would include past months too) G7
next month (July) is a 95% accuracy rate H7
following month (Aug) has a 90% accuracy rate I7
3 month rate (Sept) accuracy is 80% J7
4 months and greater (Oct) is 70% K7, L7 etc


Excel 2007<table rules="all" style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse; width: 850px; height: 223px;" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;;">Apr-2011</td><td style="text-align: center;;">May-2011</td><td style="text-align: center;;">Jun-2011</td><td style="text-align: center;;">Jul-2011</td><td style="text-align: center;;">Aug-2011</td><td style="text-align: center;;">Sep-2011</td><td style="text-align: center;;">Oct-2011</td><td style="text-align: center;;">Nov-2011</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">today's date
</td><td style="text-align: right;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">6/20/2011</td><td style="text-align: center;;">forecast accuracy %</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">95%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">90%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">80%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">70%</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">
</td><td style=";">manually entered numbers</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99FFCC;;">55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">60</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">65</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">
</td><td style="border-right: 1px solid black;;">Rolling calculated
formula</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">57</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">56.7</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">50.4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">45.5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">45.5</td></tr></tbody></table>

Sheet1​

Now if we look at the same worksheet on Sept 15

June stayed at 100%
July and August are now 100%
September is the current month at 100% J7
next month (Oct) is a 95% accuracy rate K7
following month (Nov) has a 90% accuracy rate L7
3 month rate (Dec) accuracy is 80% M7
4 months and greater (Jan, Feb) is 70% N7, O7 etc


Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: center;;">Jun-2011</td><td style="text-align: center;;">Jul-2011</td><td style="text-align: center;;">Aug-2011</td><td style="text-align: center;;">Sep-2011</td><td style="text-align: center;;">Oct-2011</td><td style="text-align: center;;">Nov-2011</td><td style="text-align: center;;">Dec-2011</td><td style="text-align: center;;">Jan-2012</td><td style="text-align: center;;">Feb-2012</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">today's date</td><td style="text-align: right;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td><td style="text-align: center;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">9/15/2011</td><td style="text-align: center;;">forecast accuracy %</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">95%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">90%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">80%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">70%</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">
</td><td style=";">manually entered numbers</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">60</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99FFCC;;">63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">70</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #99CCFF;;">63</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">
</td><td style="border-right: 1px solid black;;">Rolling calculated
formula</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">60</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">61.75</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">58.5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">56</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">44.1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">44.1</td></tr></tbody></table>

Sheet1​

the user of the worksheet would just have to type in the known values
in row 5
the formula (calculated in row 7) would look at "system date" and apply the percentages to the
current month (or past months) at 100%
next month 95%
2 months 90%
3 months 80%
4 months and greater 70%



and as always, your help is greatly appreciated

 

[Jerry Sullivan]

Hi Lefty,

This works for xl2007 and I believe it should work for xl2003 also.
This puts the calculation on Row 3 (your forecast accuracy%), so then
Row 5 is just a simple multiplication formula.

Excel Workbook
	A	B	F	G	H	I	J	K	L
1			May-11	Jun-11	Jul-11	Aug-11	Sep-11	Oct-11	Nov-11
2	today's date
3	6/20/2011	forecast accuracy %	100%	100%	95%	90%	80%	70%	70%
4		manually entered numbers	55	55	60	63	63	65	65
5		Rolling calculated	55	55	57	56.7	50.4	45.5	45.5
6		formula
Sheet2

Excel 2007

Cell Formulas
Range	Formula
H3	=LARGE({1,0.95,0.9,0.8,0.7},1+MIN(4,MAX(0,MONTH(H$1)-MONTH($A$3))))
H5	=H4*H3

The rows in your description don't seem to match the rows in your image. These formulas are based on your image.

 

[lefty38]

Wow Jerry -this is great
you did catch the description error - thanks

I assumed the 70% would of carried out indefinitely - I was wrong

I think there is an issue when the new year (2012) gets inside the window view

is there a way to extend the 70% indefinitely?
when the chart is 12 months or 24 months etc?

or no matter what date is (A1)
(to where ever the date values in row A extend too)

also
is there a way to incorporate a today() function in the formula?
if not the today() function in a referenced cell can be used too

Excel 2007<table rules="all" style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" width="1362" cellpadding="2.5px" height="228"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="border-bottom: 1px solid black;;">a September 2011 view</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">9/15/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">May-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jun-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jul-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Aug-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Sep-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Oct-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Nov-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Dec-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jan-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Feb-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Mar-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Apr-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">May-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jun-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jul-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Aug-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Sep-2012</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">this
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">section</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">works</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;"> great</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">this
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">section</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">breaks
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">down
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">should be</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">forecast accuracy %</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">95%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">90%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">80%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">100%</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">
</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70%</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">manually entered numbers</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">
</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">55</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">60</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">63</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Rolling calculated</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">
</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">60</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">63</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">61.75</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">58.5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">52</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td></tr></tbody></table>

Sheet1

here is the same formula with a "view" from a Dec 2011 date
row 3 are the actual formula results
in the "should be" row 4 are the desired formula values


Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="border-bottom: 1px solid black;;">Dec 2011
</td><td style="text-align: right;border-bottom: 1px solid black;;">view</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td><td style="text-align: right;border-bottom: 1px solid black;;">
</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FF9999;;">12/15/2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Dec-2011</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jan-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Feb-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Mar-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Apr-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">May-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jun-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Jul-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Aug-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Sep-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Oct-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Nov-2012</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Dec-2012</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">forecast accuracy %</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100%</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">should be
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">100
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">95
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">90
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">80
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70
</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td><td style="text-align: center;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #00FF99;;">70</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">manually entered numbers</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">65</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">66</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">67</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF99;;">68</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFFFF;;">Rolling calculated</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">67</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">68</td></tr></tbody></table>

Sheet2​

I hope this makes sense - I suspect it has to do with the 'Month' call out in the formula - but I could be on another planet

=LARGE({1,0.95,0.9,0.8,0.7},1+MIN(4,MAX(0,MONTH(B$2)-MONTH($A$2))))

again many thanks

​

 

[Jerry Sullivan]

I assumed the 70% would of carried out indefinitely - I was wrong

I think there is an issue when the new year (2012) gets inside the window view

is there a way to extend the 70% indefinitely?
when the chart is 12 months or 24 months etc?

or no matter what date is (A1)
(to where ever the date values in row A extend too)

The formula finds the difference in months between the two dates. If the difference is greater than 4, it will use 4 and return 70%. If the difference is less than 0, it will use 0 and return 100%.

What dates are you comparing that you find don't work? Is it possible that you have a text string instead of a date?

is there a way to incorporate a today() function in the formula?
if not the today() function in a referenced cell can be used too

Yes, just replace $A$3 in my formula with today()

 

[Jerry Sullivan]

You're right. I didn't take the year into account.

This should work...

In Cell B3, then copy across.
=LARGE({1,0.95,0.9,0.8,0.7},1+MIN(4,MAX(0,12*(YEAR(B$2)-YEAR($A$2))+MONTH(B$2)-MONTH($A$2))))

You should also be able to replace $A$2 with Today() if you prefer.

 

[lefty38]

Thank you - that worked
thank you again