Need help how to insert moving range within a formula

[nmss18]

Hello,
I have a table of market trader data activity from Jan 2012 until Dec 2014. Each user has their First login date, their last login date and their current status, whether they are currently active or inactive (which means their account was cancelled).

I am using the forecast formula to basically identify an up or down trend value. The FORECAST parameters are =FORECAST(x, Known y's values, known x's).

In my formula, I am using the entire 3 year time frame for my known Y and x values, which is technically not accurate.
For example. User #1 first logged in on March 2014 and last logged in on August 2014. He was active for 6 months only. Therefore, I cant use pre March 2014 and post Aug-2014 in the range.

I am looking for 2 things:
1. For INACTIVE Users: Is there a way I can build the formula to only use the range between the first login date and the last login date.
2. For ACTIVE Users: Is there a way I can build the formula to only use the range between the first login date and thr remainder of the time range (which in this case is Dec -14 in cell AP5)

I have a feeling this might be a little too complex but am hoping someone out there could assist.
Many thanks,
Nathan

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
5					Jan-12	Feb-12	Mar-12	Apr-12	May-12	Jun-12	Jul-12	Aug-12	Sep-12	Oct-12	Nov-12	Dec-12	Jan-13	Feb-13	Mar-13	Apr-13	May-13	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Nov-14	Dec-14		Jan-15
6	Client Name	First Login Date	Last Login Date	Current User Status	2012 January	2012 February	2012 March	2012 April	2012 May	2012 June	2012 July	2012 August	2012 September	2012 October	2012 November	2012 December	2013 January	2013 February	2013 March	2013 April	2013 May	2013 June	2013 July	2013 August	2013 September	2013 October	2013 November	2013 December	2014 January	2014 February	2014 March	2014 April	2014 May	2014 June	2014 July	2014 August	2014 September	2014 October	2014 November	2014 December		FORECAST Value
7	User #1	03/03/2014	08/23/2014	Inactive																											43,755	61,337	43,300	14,600	33,622	49,500						23216
8	User #2	08/27/2004	01/30/2012	Inactive	87,460
9	User #3	09/17/2012	01/05/2015	Active									110	142	20	110	30		114	183		75											8		349	2,923						1271
10	User #4	08/23/2012	01/06/2015	Active								446	20,037	8,899	4,917	2,221	4,355	1,617	813	1,167	233	1,087		11,362	1			6	20				1,936	1,559	1,117	15,050	44	23,130	2,121	12,054		5141
11	User #5	02/01/2014	01/02/2015	Active																										60,150	246,200	46,200	52,200	13,875	5,114	53,361	157,200	300,375	37	3,833		71250
12	User #6	12/23/2011	01/06/2015	Active	62,986	54,980	7,502	76,696	35,060	33,473	38,591	15,921	54,626	24,355	51,043	71,082	134,940	183,635	300,433	282,182	398,158	395,968	436,087	513,562	597,997	1,117,388	1,976,659	505,934	938,764	1,506,330	1,221,692	1,565,223	1,142,443	1,695,319	2,445,763	1,466,244	2,540,491	3,830,901	6,157,628	4,140,645		2919605
13	User #7	07/03/2012	01/06/2015	Active							50,650	4,000	11,055	10,911	48,106	4,400	24,794	26,962	21,160	38,806	17,843	24,887	34,500	25,672	18,379	22,759	39,925	31,872	14,855	9,600	18,921	31,341	9,718	19,032	32,803	12,394	31,237	43,222	18,879	61,677		29589
14	User #8	06/28/2013	06/24/2014	Inactive																					7	109	443	10		3,987	2,931	401	602	3,494								4574
15	User #9	06/07/2013	01/06/2015	Active																																129	88		21,352	482		12173
16	User #10	05/29/2013	01/06/2015	Active																		248	26,559	20,851	54,759	42,707	48,546	26,535	49,862	32,423	62,642	24,515	13,315	57,969	83,711	120,625	165,062	43,329	134,866	66,637		106348
17	User #11	05/29/2013	01/07/2015	Active																		1,645	10,913	20,545	45,031	52,157	66,183	72,080	51,230	36,737	33,998	24,546	20,038	10,986	19,177	25,430	114,843	23,188	129,222	23,898		58932
18	User #12	10/18/2007	01/02/2015	Active																														81,421	36,032	240,322	183,046	243,063	373,263	362,796		434917
19	User #13	10/18/2007	06/19/2014	Active	530,668	304,200	406,287	239,932	355,393	598,170	137,688	251,610	247,836	395,349	252,060	373,230	528,521	262,103	90,262	273,502	20,275	10,869	11,406		84,021	80,700	125,136	77,581	69,774	164,507	174,331	179,388	85,589	34,796								-52873
20	User #14	05/17/2011	09/12/2013	Inactive	310,157	52,255	140,442	142,159	194,588	266,266	21,939	12,794	10,500	4,078		10,068	6,025	11,711	662	35,350		4,846	51,319	65,774	155																	-168604
21	User #15	10/05/2011	06/11/2012	Inactive	111,088																																					-1226851
22	User #16	10/03/2011	01/02/2015	Active	330,702	583,284	629,720	467,799	387,284	129,257	113,350	132,200	158,850	683,730	161,180	129,213	737,701	294,033	333,759	363,215	183,299	7,220	466,196	265,138	201,361	219,785	9,848	136,203	203,084	120,675	81,699	81,327	94,671	67,705	82,303	85,615	66,853	82,371	397	78,368		14180
23	User #17	11/16/2011	06/29/2012	Inactive	526
24	User #18	11/03/2014	01/06/2015	Active																																			76,300	207,824		343732
25	User #19	11/03/2014	01/06/2015	Active																																			298,296	210,365		119503
26	User #20	11/18/2011	01/06/2015	Active	7,545,586	9,254,675	6,977,661	4,125,794	4,010,899	3,325,826	3,270,483	4,429,124	1,961,379	5,183,361	5,770,779	3,508,389	5,340,146	13,249,228	2,524,427	5,377,564	2,309,383	1,836,365	1,881,138	1,676,622	3,327,529	4,232,307	9,018,734	4,524,398	3,618,140	7,795,300	5,567,436	3,440,955	2,087,262	2,942,995	3,766,816	3,770,361	3,842,804	4,069,099	8,209,975	9,492,480		4585148
27	User #21	05/11/2011	01/06/2015	Active	1,800	4,000	5,000	28,500	69,805	30,900	22,900	36,502	4,000	46,001	49,508	25,378	79,000	11,600	31,800	29,650	16,000	36,250	51,500	46,250	16,800	32,700	47,700	29,550	97,000	76,797	30,000	72,259	30,444	7,750	16,250	24,000	48,500	107,700	43,150	77,315		57100
28	User #22	09/22/2008	11/27/2013	Inactive	3,348,907	2,104,815	6,068,180	3,533,628	4,423,185	6,552,666	3,845,619	3,926,349	6,513,652	4,483,037	5,224,747	3,826,806	2,589,699	2,732,403	6,167,700	4,361,860	8,462,862	4,531,236	1,610,547	2,767,767	4,516,192	5,777,616	5,829,133															5216481
29	User #23	09/23/2008	11/27/2013	Inactive	5,593,752	3,970,681	4,362,441	5,300,196	4,633,391	3,036,385	4,250,861	4,051,115	4,098,456	4,660,304	4,938,954	4,835,126	4,063,932	4,579,516	3,572,357	3,377,440	4,659,201	3,521,959	4,839,529	3,529,408	3,181,485	5,152,503	5,293,537															3951677
30	User #24	10/02/2012	12/29/2014	Inactive										55,000	5,000											85,100	53,330	119,231	105,915	119,500	99,500	332,500	139,000	111,729	308,330	80,900	190,271	62,566		75,454		171894
31	User #25	09/02/2011	01/06/2015	Active	171,297	275,797	1,127,176	361,211	200,852	84,890	123,846	41,842	66,817	20,420	39,541	146,234	32,354	29,646	50,140	16,738	144,641	129,069	206,265	213,226	16,417	115,011	237,224	416,799	577,305	105,890	168,859	11,515	92,171	36,643	81,925	197,538	69,211	77,201	10,100	93,351		68387
32	User #26	04/24/2014	04/24/2014	Inactive																												25,000
33	User #27	07/03/2014	07/17/2014	Inactive																															1,000
34	User #28	04/04/2014	01/06/2015	Active																												2,499,911	4,753,828	4,434,474	4,654,497	8,379,313	5,388,593	7,076,356	4,133,519	6,025,628		6780802
Example Sheet

Cell Formulas
Range	Formula
AR7	=IFERROR(FORECAST($AR$5,G7:AP7,$G$5:$AP$5),"")
AR8	=IFERROR(FORECAST($AR$5,G8:AP8,$G$5:$AP$5),"")
AR9	=IFERROR(FORECAST($AR$5,G9:AP9,$G$5:$AP$5),"")
AR10	=IFERROR(FORECAST($AR$5,G10:AP10,$G$5:$AP$5),"")
AR11	=IFERROR(FORECAST($AR$5,G11:AP11,$G$5:$AP$5),"")
AR12	=IFERROR(FORECAST($AR$5,G12:AP12,$G$5:$AP$5),"")
AR13	=IFERROR(FORECAST($AR$5,G13:AP13,$G$5:$AP$5),"")
AR14	=IFERROR(FORECAST($AR$5,G14:AP14,$G$5:$AP$5),"")
AR15	=IFERROR(FORECAST($AR$5,G15:AP15,$G$5:$AP$5),"")
AR16	=IFERROR(FORECAST($AR$5,G16:AP16,$G$5:$AP$5),"")
AR17	=IFERROR(FORECAST($AR$5,G17:AP17,$G$5:$AP$5),"")
AR18	=IFERROR(FORECAST($AR$5,G18:AP18,$G$5:$AP$5),"")
AR19	=IFERROR(FORECAST($AR$5,G19:AP19,$G$5:$AP$5),"")
AR20	=IFERROR(FORECAST($AR$5,G20:AP20,$G$5:$AP$5),"")
AR21	=IFERROR(FORECAST($AR$5,G21:AP21,$G$5:$AP$5),"")
AR22	=IFERROR(FORECAST($AR$5,G22:AP22,$G$5:$AP$5),"")
AR23	=IFERROR(FORECAST($AR$5,G23:AP23,$G$5:$AP$5),"")
AR24	=IFERROR(FORECAST($AR$5,G24:AP24,$G$5:$AP$5),"")
AR25	=IFERROR(FORECAST($AR$5,G25:AP25,$G$5:$AP$5),"")
AR26	=IFERROR(FORECAST($AR$5,G26:AP26,$G$5:$AP$5),"")
AR27	=IFERROR(FORECAST($AR$5,G27:AP27,$G$5:$AP$5),"")
AR28	=IFERROR(FORECAST($AR$5,G28:AP28,$G$5:$AP$5),"")
AR29	=IFERROR(FORECAST($AR$5,G29:AP29,$G$5:$AP$5),"")
AR30	=IFERROR(FORECAST($AR$5,G30:AP30,$G$5:$AP$5),"")
AR31	=IFERROR(FORECAST($AR$5,G31:AP31,$G$5:$AP$5),"")
AR32	=IFERROR(FORECAST($AR$5,G32:AP32,$G$5:$AP$5),"")
AR33	=IFERROR(FORECAST($AR$5,G33:AP33,$G$5:$AP$5),"")
AR34	=IFERROR(FORECAST($AR$5,G34:AP34,$G$5:$AP$5),"")

 

[RickXL]

Hi,

Unfortunately, I have never used the FORECAST function but a quick test would seem to indicate that it already is programmed to ignore any missing values.

Do you have an example of a forecast value that has been calculated incorrectly?

 

[nmss18]

Interesting. Where did you see that?
Also, since I posted this it seems that I would like to use the SLOPE function. I would like the function to include the 0 values. Perhaps I could put a .01 value in each blank cell, but that could get very messy.

 

[RickXL]

I just tried it out.

Create two columns with numbers from 0 to 9 in each. (If you put the same numbers in each then the answer will be easy to predict mentally.)
Use FORECAST to predict the result.
Then delete some values and see what happens.
Another test would be to replace a blank y-value with a zero. The forecast will change (assuming there is an associated x-value.)