Forum Discussion
Solved
Automate chart data selection
I would like to use a formula to automate Select data..., so that it detects the first and last non-zero value in a series range, then plots one additional category (with series value = 0) before and...
You may add two named ranges
Xrange: =INDEX(Sheet1!$A:$A,AGGREGATE(15,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)-1):INDEX(Sheet1!$A:$A,AGGREGATE(14,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)+1) Yrange =INDEX(Sheet1!$B:$B,AGGREGATE(15,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)-1):INDEX(Sheet1!$B:$B,AGGREGATE(14,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)+1)and add use them for chart data
Xrange
Yrange (Series):
mtarler This remarkable solution achieves much of what I need, but the array formulas seem to "run out of steam" and fail to detect non-zero values in subsequent rows (highlighted in attached workbook). Changing B20 to a number > 0 extends the array formula results to the next zero value, but not to the last zero as it should. Thank you for your help.
