I am using the STOCKHISTORY function to find the minimum value during a set period. On occasion and for some stocks, there may be errors or missing data on a given day. This returns #N/A value, I would very much like to eradicate these errors and/or count the errors as zero. Period of time covered in this instance is previous 365 days, C17 is referencing the 'stock' ticker.
Current formula is shown below:
=MIN(STOCKHISTORY(C17,TODAY()-365,TODAY(),0))
This returns #N/A due to missing data on some days in the data pull, an assumption based on previous experience with using STOCKHISTORY to actually pull daily data going back between 1 and 10 years. Ideally I would like to keep this contained within one cell, rather than pulling the data and then removing the #N/A errors which I have done in the past.
Appreciate any advice, although I have been working my way around Excel formulas recently, this one has me stumped.
MIN ignores empty values, so you just need to use IFERROR to replace NA with "".
e.g below works:
=MIN(IFERROR(STOCKHISTORY("rddt",TODAY()-365,TODAY(),0),""))