Dynamic Chart - Show Range Between Values
The following example uses a combination of defined names and array formulas to dynamically chart a range between a high and low value. In the screenshot below, data is entered into the yellow shaded cells. The chart source is within the white grid range E3:F22.
The first step to creating the chart is to build four named ranges. Three of the four named ranges will act as components of the array formulas that make up the chart source. Each of the formulas can be viewed in the screenshots of the named range dialog boxes below:
The first named range is called "Data":
The second named range is called "Labels":
The third named range is called "List":
The fourth and final named range is called "Values":
Two array formulas make up the chart source. To enter an array formula, select the entire range, enter the formula into the formula bar, and then hit the CONTROL - SHIFT - ENTER keys at the same time.
The first array formula below covers the range E3:E22.
=IF(ISERR(SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3)))," ", INDEX(List,SMALL(IF(Data=F3,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($F$3:F3,F3))))
The second array formula below covers the range F3:F22.
=IF(ISERR(SMALL(IF(Data>=$H$3,IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),
ROW(INDIRECT("1:"&ROWS(Data))))),"",INDEX(Data,SMALL(IF(Data>=$H$3,
IF(Data<=$H$2,ROW(INDIRECT("1:"&ROWS(Data))))),ROW(INDIRECT("1:"&ROWS(Data))))))
Finally, it's time to create the chart. Assuming that the file name is "RangeBetween.xls", the chart source dialog box looks like this:
