Hi
So as an example if i changed all the Column Assays for march to something else (bob) and then selected Column in the drop down you get #div/0! errors - is that want you want to stop?
If so could you just iferror & NA()?
=IFERROR(IF($C$5="(All)",AVERAGEIFS(Table1[Time],Table1[Date],">="&Metrics!G11,Table1[Date],"<="&EOMONTH(Metrics!G11,0)),AVERAGEIFS(Table1[Time],Table1[Assay],Metrics!$C$5,Table1[Date],">="&Metrics!G11,Table1[Date],"<="&EOMONTH(Metrics!G11,0))),NA())
The graph will just have a blank area
In the data grid i think you will need to use aggregate instread of Min/max/Average as aggregate can ignore errors
=IFERROR(IF(AGGREGATE(15,6,$H$9:$H$20,1)=$H9,AGGREGATE(15,6,$H$9:$H$20,1),NA()),NA())
=IFERROR(IF(AGGREGATE(14,6,$H$9:$H$20,1)=$H9,AGGREGATE(14,6,$H$9:$H$20,1),NA()),NA())
=AGGREGATE(1,6,$H$9:$H$20)
Let me know if i am on the right lines
Purfleet
Hi Purfleet,
Thank you so much for your response. I really appreciate it. I'll look into including the adding the aggregate instead of the max/min. I'm not sure if your suggestion about altering the assay column will solve my issue. Given the current pandemic, in most places no work is being done. 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.
Li