-1

I want to figure out how to handle N/A in formulas that use arrays. I'm using the Bloomberg Data History Add-In BDH to get the latest EX-DIV date. But in cases where there is an N/A in the array, the output is N/A instead of the latest EX-DIV date.

This is the formula I am using:

=IF(AN6="","",INDEX(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true"),MATCH(MIN(ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2)),ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2),0)))
  • AM6 is the sedol
  • AN6 is a check for data
  • A2 is today's date
StorymasterQ
  • 264
  • 2
  • 17
ninjagirl
  • 23
  • 2

1 Answers1

1

Have you tried encapsulating the entire thing with IFERROR()? It would become something like

=IFERROR(IF(AN6="","",INDEX(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true"),MATCH(MIN(ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2)),ABS(BDS(AM6,"DVD_HIST_ALL","StartCol=2","EndCol=2","array=true")-$A$2),0))),$A$2)
StorymasterQ
  • 264
  • 2
  • 17