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.
      • scheij's avatar
        scheij
        Copper Contributor
        Aug 11, 2020

        SergeiBaklan 

        I made the recommended changes, but the caution message (pasted above) still appears when the formula finds only zero values, as in Chart 4, Xrange4, and Yrange4.   Also, the category axis label does not show "No Data" as expected in the IFERROR function in named formula, Xrange4.  I noticed S133:S134 are entered as array formulas, but I was not able to do that in the Name Manager with shift-ctrl-enter.  Copying any other value from row 4 to F4 produces the expected results with no warning.  Any suggestions would again be appreciated.

  • 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