Forecast Function - Negative Values

ChetanPuri

Board Regular
Joined
Sep 5, 2018
Messages
74
Office Version
  1. 365
Platform
  1. Windows
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
DEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQ
1Jul-2019Aug-2019Sep-2019Oct-2019Nov-2019Dec-2019Jan-2020Feb-2020Mar-2020Apr-2020May-2020Jun-2020TotalJul-2020Aug-2020Sep-2020Oct-2020Nov-2020Dec-2020Jan-2021Feb-2021Mar-2021Apr-2021May-2021Jun-2021TotalJul-2021Aug-2021Sep-2021Oct-2021Nov-2021Dec-2021Jan-2022Feb-2022Mar-2022Apr-2022May-2022Jun-2022TotalJul-2022Aug-2022Sep-2022Oct-2022Nov-2022Dec-2022Jan-2023Feb-2023Mar-2023Apr-2023May-2023Jun-2023TotalJul-2023Aug-2023Sep-2023Oct-2023Nov-2023Dec-2023Jan-2024Feb-2024Mar-2024
150.000.000.000.000.00102,336.0017,056.0017,056.0017,056.0017,056.0017,056.0014,812.66202,428.6622,715.5622,715.5622,715.5622,715.5622,715.5622,715.5622,715.5622,715.560.000.000.000.00181,724.4816,637.7316,637.7316,637.7316,637.7316,637.7316,637.7316,637.7316,637.7316,637.7316,637.7316,637.737,255.64190,270.6713,189.2913,189.290.0026,378.5813,189.2913,189.2913,189.2913,189.290.000.000.000.00105,514.320.000.000.000.000.000.000.000.00-672.22
180.000.000.000.000.0082,498.0013,749.6713,749.6713,749.6713,749.6713,749.6783,971.29235,217.6419,963.0719,963.0719,963.0719,963.0719,963.0719,963.0719,963.0719,963.070.000.000.000.00159,704.5618,718.1218,718.120.000.000.000.000.000.000.000.000.000.0037,436.240.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-1,703.83
190.000.000.000.000.0066,032.0011,005.4211,005.4211,005.4211,005.4211,005.4239,299.31160,358.4119,670.1819,670.1819,670.1819,670.1819,670.1819,670.1819,670.1819,670.180.000.000.000.00157,361.4418,542.7518,542.750.000.000.000.000.000.000.000.000.000.0037,085.500.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-971.65
200.000.000.000.000.0095,145.0015,857.5015,857.5015,857.5015,857.5015,857.5072.39174,504.8919,205.5019,205.5019,205.5019,205.5019,205.5019,205.5019,205.5019,205.500.000.000.000.00153,644.0012,591.7312,591.7312,591.7312,591.7312,591.7312,591.7312,591.7312,591.7312,591.7312,591.7312,591.7339,634.63178,143.660.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-11,483.08
230.000.000.000.000.00101,196.0016,865.9216,865.9216,865.9216,865.9216,865.9211,747.21197,272.8123,952.8523,952.8523,952.8523,952.8523,952.8523,952.8523,952.8523,952.850.000.000.000.00191,622.8018,175.6318,175.630.000.000.000.000.000.000.000.000.000.0036,351.260.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.000.00-1,507.97
240.000.000.000.000.000.0055,623.807,184.1411,547.7312,099.238,773.6514,995.69110,224.2411,142.978,933.6911,321.8113,194.439,041.7010,486.4411,128.508,845.890.000.000.000.0084,095.438,840.0015,144.4511,134.1813,631.659,008.259,034.9010,863.179,262.996,659.5012,977.157,961.068,749.14123,266.442,733.4711,517.100.007,302.065,298.736,638.160.000.001,302.03720.19905.981,037.9737,455.690.000.000.000.000.000.000.000.00-411.72
260.000.000.000.000.000.0055,623.807,184.1411,547.7312,099.238,773.6514,995.69110,224.2411,142.978,933.6911,321.8113,194.439,041.7010,486.4411,128.508,845.890.000.000.000.0084,095.438,840.0015,144.4511,134.1813,631.659,008.259,034.9010,863.179,262.996,659.5012,977.157,961.068,749.14123,266.443,416.8314,396.380.009,127.576,623.428,297.707,795.457,509.116,510.163,600.934,529.925,189.8776,997.340.000.000.000.000.000.000.000.00-2,396.84
270.000.000.000.000.000.0055,623.807,184.1411,547.7312,099.238,773.6514,995.69110,224.2411,142.978,933.6911,321.8113,194.439,041.7010,486.4411,128.508,845.890.000.000.000.0084,095.438,840.0015,144.4511,134.1813,631.659,008.259,034.9010,863.179,262.996,659.5012,977.157,961.068,749.14123,266.443,416.8314,396.380.009,127.576,623.428,297.707,795.457,509.110.000.000.000.0057,166.460.000.000.000.000.000.000.000.00-291.04
280.000.000.000.000.000.0033,374.284,310.486,928.647,259.545,264.198,997.4166,134.546,685.785,360.216,793.097,916.665,425.026,291.866,677.105,307.540.000.000.000.0050,457.265,304.009,086.676,680.518,178.995,404.955,420.946,517.905,557.793,995.707,786.294,776.645,249.4873,959.862,050.108,637.830.005,476.543,974.054,978.623,118.183,003.642,604.061,440.371,811.972,075.9539,171.310.000.000.000.000.000.000.000.00-35.92
Forecast
Cell Formulas
RangeFormula
E1:O1,BI1:BO1,AU1:BE1,AG1:AQ1,S1:AC1E1=EOMONTH(D1,1)
BQ1BQ1=DATE(YEAR(BO1),MONTH(BO1)+2,0)
BQ15,BQ18:BQ20,BQ23:BQ24,BQ26:BQ28BQ15=FORECAST.ETS(BQ$1,$D15:$BO15,$D$1:$BO$1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BH1:BO1,BQ1:BT1Expression=$D$2>#REF!textNO
AT1:BF1Expression=$D$2>#REF!textNO
AF1:AR1Expression=$D$2>#REF!textNO
D1:P1,R1:AD1Expression=$D$2>#REF!textNO
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0

Forum statistics

Threads
1,223,880
Messages
6,175,154
Members
452,615
Latest member
bogeys2birdies

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
Back
Top