excelexcel-formulapredictionforecastingets

FORECAST ETS Functionality is giving #NUM Error if data is missing for at the end of the month

I am trying to forecast for 20days,starting from 01st April 2023, in stock prices of a company based on last 6 months' period i.e. from Oct'22 to Mar'23. However the since there was no trading on 31st Dec 2022, its data is blank leading to #NUM Error.

enter image description here

Solution

  • You could remove any rows that don't have any data by deleting them

    Or add a cleaned data column that takes the previous day's value if there's no value available for that day. For example, for 31st Dec 2022, you could take the value for 30th Dec 2022. For C2:

    =IF(ISBLANK(B2),C1,B2)

    Or you could take the middle value between the previous and next day which might be a more accurate value to use. This works best if there aren't two subsequent days without values, as they won't output the midpoint but rather just the previous/next day's value. For C2:

    =IF(ISBLANK(B2),MEDIAN(B1,B3),B2)