Skip rows/values for Y-axis

Hello,
I've made a Dashboard (see attachement), where I can generate trends based on choice options. The table on tab "calculation" will sort the list based on the choice of Parameter.
So far so good.

The problem is, that the Y-axis always includes 0 as lowest value. This is because in the table, row 9 to 24 contains formulas, and are not "empty".
When selecting Parameter D or E, you can not see much difference in the trend columns, because the values are close to eachother.

When I hide row 9 to 24, or when I deleted all values from row 9 to 24, the Y-axis is automatically adjusted to the values. This is show in the picture of the trend below the actual trend.
The Y-axis then changed to a range of 59 - 70 instead of 0 - 80.
It is easy to hide rows, or to delete values, to solve this issue.
But the data is imported with a macro in another workbook. It could contain new varieties (E, F etc). The dashboard won't be updated then. I have to manually show the rows again.

I tried with ("") or #N/B (in Dutch) as outcome of the formula, but doesn't work.


My question is: is there a way that the y-axis values are not taking into account when the cel contains a formula? So that the Y-axis never show 0 as lowest value.

Hi

I've created some dynamic ranges to display on chart and also add macro which change minimum on Y axis.
Clicking radios which sorts data run recalculation on minimum (as minimum value rounded to full 10s).


Check attached file.

MyVals are range of sorted values BUT height of this range is depends of NoOfVals.
NoOfVals 'counts' how many row have to be displayed, so, how many (how height) MyVals have to be, then you avoid displaying zeros which is not related to data.
ColM range of labels for X-axis (wide as need, based on NoOfVals)
I've rounded minimum for nice look of Yaxis. You can change this as you like:

Please Login or Register  to view this content.

can be

Please Login or Register  to view this content.

for lowest value.


Check attached file. I've tweak a bit NoOfVals reference.

A different approach; using a pivot table (primarily because they resize themselves).
In the attached, on the Dashboard sheet, below row 31, there is:
1. A straightforward pivot table, whose values are the same as yours (almost - more on that later).
This pivot data is based only on the Data sheet.
The calculation sheet isn't used at all in this solution - it can be deleted! (And nor are defined/named ranges)
2. A chart (it's not a pivot chart (they don't let you plot a subset of columns))
3. A timeline at cell B32

Instead of choosing an option button you right-click a header (Counter, Parameter B etc.) in the pivot table to change what's plotted and the sort order in the pivot table & chart. We can change that if you want.
Instead of adding an RAS to column A of the calculation sheet to see it on the dashboard you choose what to see in the pivot's own dropdown in cell B42 (I added a new RAS (vk) for testing and comparison while developing).
In cell C40 there's a pivot dropdown currently filtering out blanks; this is equivalent to your formula in column B of the calculation sheet which looks for data in column E (Parameter C) of the Data sheet to decide what to include in your summary.
The timeline is there because I noticed you added a column to the Data sheet which looks at dates in the calculation sheet in cell E1 and E3, so I'm guesssing you want to limit the plot to certain date ranges. The timeline does this for you without the need for that extra column in the Data sheet - and it lets you pick date ranges in a simple way with click and drag, be it in days/months/quarters/years.

There's some macro code to update the chart (only 38 lines) which allows you to add/remove columns of summary data from the pivot table (occasionally you might see no plot at all in the chart after you remove a column, and you'll probably see two plots if you add a column to the pivot table, but this is put right the moment you right-click on one of the headers on the pivot table, which, incidentally, you can rename).
The tehnique used to decide what to show in the chart is a little different from yours, where you only have one series in the chart, here I've put as many series in the chart as there are columns in the pivot and just choose which one to show; this means that you can change colours, labels, even chart types for individual columns, and they'll stay.

A word on the small differences at the bottom line of the pivot table:
The values are exactly the same for the Counter and Parameter B columns (they're just totals), but for the rest (averages) they are slightly different. This is because your averages are an average-of-averages, whereas the pivot table average is an average of all the underlying values.
To illustrate, Variety B had only one value for Paramter E in the whole Data table in cell G56 (highlighted) (it was 94.11% but I changed it to 10%). That value appears in your Dashboard summary table as 10%, this 10% is then included in the average of averages and brings it down to 72% - that 10% value has too much weight (it's only one value among some 55 values). For the same data, the pivot averages all the underlying values returning 92%. I don't know which you prefer.