Bar Chart Results Product Portfolio Optimizer

Hi!

I have made a product mix optimization tool for 20 products and I would like to make a bar chart of the results so that there would be products in the x-axis and the amount of every product in the portfolio in the y-axis.

The problem occus when the portfolio includes less than 20 products. For instance if you input just 5 products to the tool, the automatized bar chart will not work because it always takes the data from the range that includes space for 20 products and their amounts in the portfolio.

So my question is: how to plot to bar chart only cells that include text or numbers and ignore all the "" cells (I used if formula for the data range for the bar chart --> if the input data contains numbers or text, display them on the data range for the bar chart, otherwise display "")?

Thank you in advande.

Hi,

I'd format the data set as a table and have the chart refer to the whole table.
Then, make sure that the table contains data only for the products you want to display in the chart. This way, when you add/remove products from the data table, the chart will update automatically, because it is referring to a data table, not to a fixed data range.

Hope this helps!