Hessel de Walle's Blog

I was struggling with a simple column graph. I was just wondering if there is a formula in Excel which can indicate whether a row is hidden or not. Without using VBA of course. Here are my data: Year Apples 2001 10 2002 11 2003 12 2004 13 2005 14 2006 15 2007 16 2008 17 2009 18 2010 19 2011 20 2012 21 2013 22 2014 23 2015 24 What I wanted to achieve is a graph like this which does not show the rows I hid, in this example the years 2005-2008, but where I still can see the X-axis values. In this chart I checked the box Show data in hidden rows and columns Otherwise I would not get the years 2005-2008 on the X-axis. So I needed a function to check wether a row is hidden or not in order to manipulate the data in column B:B. Here is my solution. I turned my data list into a table. Then I added a third column with this form...

What to show? Thirty six charts or just one dynamic one? I created I chart which shows the choice of two parameters: cumulatives AVERAGE, MIN or MAX and months JAN through DEC. So, in this case we have thirty six sets of data we can choose from. Take your pick! I needed just one complex name: MONTH: =OFFSET(temp1!$B$2;0;MATCH(temp!$Q$2;monthnames;0)-1;temp!XER1-1705;1) The rest you can easily figure out yourself. You can download  ExcelMoving.xlsx  via: https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing