Forecast Function - Negative Values

[ChetanPuri]

Hello Excel Community,

I have a question regarding excel forecast function, I am using prior year actual data from Column D 2 to BO 20 but when I use forecast.ets function in column BQ 2 onwards, forecast for March 2024 to June 2025 is in negative. Does anyone know, how to fix the issue or to ignore all negatives and 0 values so my forecast only have positive numbers.

Book1
	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	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ
1	Jul-2019	Aug-2019	Sep-2019	Oct-2019	Nov-2019	Dec-2019	Jan-2020	Feb-2020	Mar-2020	Apr-2020	May-2020	Jun-2020	Total		Jul-2020	Aug-2020	Sep-2020	Oct-2020	Nov-2020	Dec-2020	Jan-2021	Feb-2021	Mar-2021	Apr-2021	May-2021	Jun-2021	Total		Jul-2021	Aug-2021	Sep-2021	Oct-2021	Nov-2021	Dec-2021	Jan-2022	Feb-2022	Mar-2022	Apr-2022	May-2022	Jun-2022	Total		Jul-2022	Aug-2022	Sep-2022	Oct-2022	Nov-2022	Dec-2022	Jan-2023	Feb-2023	Mar-2023	Apr-2023	May-2023	Jun-2023	Total		Jul-2023	Aug-2023	Sep-2023	Oct-2023	Nov-2023	Dec-2023	Jan-2024	Feb-2024		Mar-2024
15	0.00	0.00	0.00	0.00	0.00	102,336.00	17,056.00	17,056.00	17,056.00	17,056.00	17,056.00	14,812.66	202,428.66		22,715.56	22,715.56	22,715.56	22,715.56	22,715.56	22,715.56	22,715.56	22,715.56	0.00	0.00	0.00	0.00	181,724.48		16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	16,637.73	7,255.64	190,270.67		13,189.29	13,189.29	0.00	26,378.58	13,189.29	13,189.29	13,189.29	13,189.29	0.00	0.00	0.00	0.00	105,514.32		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-672.22
18	0.00	0.00	0.00	0.00	0.00	82,498.00	13,749.67	13,749.67	13,749.67	13,749.67	13,749.67	83,971.29	235,217.64		19,963.07	19,963.07	19,963.07	19,963.07	19,963.07	19,963.07	19,963.07	19,963.07	0.00	0.00	0.00	0.00	159,704.56		18,718.12	18,718.12	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	37,436.24		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-1,703.83
19	0.00	0.00	0.00	0.00	0.00	66,032.00	11,005.42	11,005.42	11,005.42	11,005.42	11,005.42	39,299.31	160,358.41		19,670.18	19,670.18	19,670.18	19,670.18	19,670.18	19,670.18	19,670.18	19,670.18	0.00	0.00	0.00	0.00	157,361.44		18,542.75	18,542.75	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	37,085.50		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-971.65
20	0.00	0.00	0.00	0.00	0.00	95,145.00	15,857.50	15,857.50	15,857.50	15,857.50	15,857.50	72.39	174,504.89		19,205.50	19,205.50	19,205.50	19,205.50	19,205.50	19,205.50	19,205.50	19,205.50	0.00	0.00	0.00	0.00	153,644.00		12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	12,591.73	39,634.63	178,143.66		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-11,483.08
23	0.00	0.00	0.00	0.00	0.00	101,196.00	16,865.92	16,865.92	16,865.92	16,865.92	16,865.92	11,747.21	197,272.81		23,952.85	23,952.85	23,952.85	23,952.85	23,952.85	23,952.85	23,952.85	23,952.85	0.00	0.00	0.00	0.00	191,622.80		18,175.63	18,175.63	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	36,351.26		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-1,507.97
24	0.00	0.00	0.00	0.00	0.00	0.00	55,623.80	7,184.14	11,547.73	12,099.23	8,773.65	14,995.69	110,224.24		11,142.97	8,933.69	11,321.81	13,194.43	9,041.70	10,486.44	11,128.50	8,845.89	0.00	0.00	0.00	0.00	84,095.43		8,840.00	15,144.45	11,134.18	13,631.65	9,008.25	9,034.90	10,863.17	9,262.99	6,659.50	12,977.15	7,961.06	8,749.14	123,266.44		2,733.47	11,517.10	0.00	7,302.06	5,298.73	6,638.16	0.00	0.00	1,302.03	720.19	905.98	1,037.97	37,455.69		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-411.72
26	0.00	0.00	0.00	0.00	0.00	0.00	55,623.80	7,184.14	11,547.73	12,099.23	8,773.65	14,995.69	110,224.24		11,142.97	8,933.69	11,321.81	13,194.43	9,041.70	10,486.44	11,128.50	8,845.89	0.00	0.00	0.00	0.00	84,095.43		8,840.00	15,144.45	11,134.18	13,631.65	9,008.25	9,034.90	10,863.17	9,262.99	6,659.50	12,977.15	7,961.06	8,749.14	123,266.44		3,416.83	14,396.38	0.00	9,127.57	6,623.42	8,297.70	7,795.45	7,509.11	6,510.16	3,600.93	4,529.92	5,189.87	76,997.34		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-2,396.84
27	0.00	0.00	0.00	0.00	0.00	0.00	55,623.80	7,184.14	11,547.73	12,099.23	8,773.65	14,995.69	110,224.24		11,142.97	8,933.69	11,321.81	13,194.43	9,041.70	10,486.44	11,128.50	8,845.89	0.00	0.00	0.00	0.00	84,095.43		8,840.00	15,144.45	11,134.18	13,631.65	9,008.25	9,034.90	10,863.17	9,262.99	6,659.50	12,977.15	7,961.06	8,749.14	123,266.44		3,416.83	14,396.38	0.00	9,127.57	6,623.42	8,297.70	7,795.45	7,509.11	0.00	0.00	0.00	0.00	57,166.46		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-291.04
28	0.00	0.00	0.00	0.00	0.00	0.00	33,374.28	4,310.48	6,928.64	7,259.54	5,264.19	8,997.41	66,134.54		6,685.78	5,360.21	6,793.09	7,916.66	5,425.02	6,291.86	6,677.10	5,307.54	0.00	0.00	0.00	0.00	50,457.26		5,304.00	9,086.67	6,680.51	8,178.99	5,404.95	5,420.94	6,517.90	5,557.79	3,995.70	7,786.29	4,776.64	5,249.48	73,959.86		2,050.10	8,637.83	0.00	5,476.54	3,974.05	4,978.62	3,118.18	3,003.64	2,604.06	1,440.37	1,811.97	2,075.95	39,171.31		0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-35.92
Forecast

Cell Formulas
Range	Formula
E1:O1,BI1:BO1,AU1:BE1,AG1:AQ1,S1:AC1	E1	=EOMONTH(D1,1)
BQ1	BQ1	=DATE(YEAR(BO1),MONTH(BO1)+2,0)
BQ15,BQ18:BQ20,BQ23:BQ24,BQ26:BQ28	BQ15	=FORECAST.ETS(BQ$1,$D15:$BO15,$D$1:$BO$1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BH1:BO1,BQ1:BT1	Expression	=$D$2>#REF!	text	NO
AT1:BF1	Expression	=$D$2>#REF!	text	NO
AF1:AR1	Expression	=$D$2>#REF!	text	NO
D1:P1,R1:AD1	Expression	=$D$2>#REF!	text	NO

 

[etaf]

not an expert on the forecast function used a few times
BUT in the help section it says

Timeline: is the independent array or range of numeric data. The dates in the timeline must have a consistent step between them and can't be zero.

you have zeros in the range....

• Timeline Required. The independent array or range of numeric data. The dates in the timeline must have a consistent step between them and can’t be zero. The timeline isn't required to be sorted, as FORECAST.ETS will sort it implicitly for calculations. If a constant step can't be identified in the provided timeline, Forecast.ETS will return the #NUM! error. If timeline contains duplicate values, FORECAST.ETS will return the #VALUE! error. If the ranges of the timeline and values aren't of same size, FORECAST.ETS will return the #N/A error.