Forum Discussion
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 after them, as shown below and in the attached file. I considered INDEX/MATCH with OFFSET, but that led to a dead end. Any suggestions? Thanks.
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):
15 Replies
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):
The two named ranges you recommended work great, except in cases with only zero values, when Excel returns this warning. For example, see X4range and Y4range in Chart 4.
I unsuccessfully tried adding an IF function to create an X category labeled "No Data" and a Y value that's far beyond the expected range.
X4range: =IF(F5>0,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),"NO DATA")Y4range =IF(F4>0,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),99988)Any suggestions to accommodate non-zero values and avoid the warnings would be appreciated. The workbook is attached. Thanks.
- Yes, that's a better fix. Thank you.
scheij I can't change the automatic function of the select data function but here is a pair of formulas you can paste in a new pair of columns that will 'pull' that range of data for you. I used a Defined Name "dRange" to define where your data (y-data) is located. You can either create that NAME in the Name Manager or replace each occurrence with the range you need:
=IFERROR(IF(ROW()=1,"New X",INDEX(OFFSET(dRange,MATCH(TRUE,dRange>0,0)-2,-1,MATCH(TRUE,dRange>0)-MATCH(TRUE,dRange>0,0)+3),COUNT(OFFSET($A$1,0,COLUMN()-1,ROW()-1))+1)),"")=IFERROR(IF(ROW()=1,"New Y",INDEX(OFFSET(dRange,MATCH(TRUE,dRange>0,0)-2,0,MATCH(TRUE,dRange>0)-MATCH(TRUE,dRange>0,0)+3),COUNT(OFFSET($A$1,0,COLUMN()-1,ROW()-1))+1)),"")If placed starting in Row 1 it will add the "New X" and "New Y" titles.
NOTE: these are both ARRAY formulas and you must hit CTRL-SHIFT-ENTER after you paste the text in
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.
