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

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 12, 2020

        scheij 

        1) I don't see such error on my version of Excel, however formulas for X4 and Y4 return extra zero, my mistake. Corrected,  -1 and +1 after the AGGREGATE changed on -0 and +0. Or they are could be removed at all.

        2) S133 and S134 shall return arrays, these formulas are for dynamic ranges. I assumed you are on Excel with dynamic arrays since in comments XLOOKUP and UNIQUE are mentioned. If open in Excel without dynamic arrays these formulas will be shown as array ones. That's okay, but don't Ctrl+Shift+Enter them in named formulas, that doesn't work. In Name Manager keep them as regular formulas.

         

        Please check attached file. Right now I have no access to pre-DA Excel and can't test on it, hope will work. On DA Excel it works.

Resources