Searchable Drop-Down List Graph with No data forTimepoints

[llogballig]

Hi All,

I've created a searchable drop-down list that populates a graph based on the assay selected. I'm trying to anticipate the event where no assays are done for an entire month or months. I'd like to compute "NA" for the time column if for any month(s) there is no data and still have the graph populate properly. I tried adding iterations of =if"&Metrics!G9,Table1[Date],"=””,NA() with no success. So say for the months of March and May there will be no data for those two months. I'd like to amend the formula so that an "NA" populates for the time column for those months so that the graph display correctly. Thanks again for all your help. I have attached the workbook for your review. Any help you guys could provide will be greatly appreciated.

[ben_hensel]

Basically all you need to do is let AVERAGEIFS throw a DIV/0 error for the situation where there are no tests in the date range, hence 'value = 0' / zero entries;
and then wrap the AVERAGEIFS with IFERROR("function", NA()) to swap out the division-by-zero error code for the Not Applicable Error code to feed your graph.

Thus:

Please Login or Register  to view this content.

In H9 and pull down.

Notice I also stuck the IF() test for the "Assay Type" inside the AVERAGEIFS instead of outside. I don't think this will have a meaningful impact on the function, but I think it makes it more human-redeable simply because it's shorter.

If that delivers what you want for the AVERAGEIFS, then you can do the same thing for minimum-in-range and maximum-in-range with MINFIS and MAXIFS, fed the exact same arguments as AVERAGEIFS, but those will report zero as a result instead of an error code in the "no data" case. You would probably need to wrap another boolean for that case somehow if you want it to throw an error instead.
(If you need more backwards compatibility, rather than the native MINIFS() function you could use the {MIN(IF)} array construction; ditto AVERAGEIFS() and the {AVERAGE(IF)} array).