Forum Discussion

scheij's avatar
scheij
Copper Contributor
Jul 09, 2020
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...
Charting
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 09, 2020

    scheij 

    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):

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 09, 2020

scheij 

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):

  • scheij's avatar
    scheij
    Copper Contributor
    Aug 05, 2020

    SergeiBaklan 

    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.

    • scheij's avatar
      scheij
      Copper Contributor
      Aug 06, 2020
      Yes, that's a better fix. Thank you.
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Aug 06, 2020

      scheij 

      We may wrap formulas with IFERROR as

      =IFERROR(INDEX(LF!$K:$K,AGGREGATE(15,6,1/(Freq4<>0)*ROW(Freq4),1)-1):INDEX(LF!$K:$K,AGGREGATE(14,6,1/(Freq4<>0)*ROW(Freq4),1)+1),"No Data")

and

=IFERROR(INDEX(LF!$F:$F,AGGREGATE(15,6,1/(Freq4<>0)*ROW(Freq4),1)-1):INDEX(LF!$F:$F,AGGREGATE(14,6,1/(Freq4<>0)*ROW(Freq4),1)+1),99988)

      Result is

Resources