With the formula below I've created a rolling chart that only shows how ever many days I have entered into AW3. So when a new data entry is inserted, it drops off the last. Here's my problem, I have these charts made for several different areas and I need to create a data set that shows the average of them all with a chart as well. When I use the offset formula below for my chart data, it grabs the formulas I used to create the average even if there is no returned value. My offset chart data will not ignore the #N/A values, nor will it ignore them if i make it return a "" value.


=OFFSET(Master!$AU$3,COUNTA(Master!$AU:$AU)-(Master!$AW$3+2),0,Master!$AW$3,1)

=IF(ISERROR(AVERAGE(AP3,AK3,AF3,AA3,V3,Q3,L3,G3,B3)),NA(),AVERAGE(AP3,AK3,AF3,AA3,V3,Q3,L3,G3,B3)) to calculate the average.

Production Charts.xlsx