Doorgaan naar hoofdcontent

Posts

Posts uit maart, 2015 weergeven

Excel: Hidden Rows in Charts

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:

YearApples200110200211200312200413200514200615200716200817200918201019201120201221201322201423201524
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 formula:

=IF(SUBTOTAL(103;[@Apples])=0;NA();[@Apples])

Without the table the formula would have looked like this:

=IF(SUBTOTAL(103;B2)=0;NA();B2)

The formula SUBTOTAL(103;[@Apples]) performs a COUNTA without …

Excel: Which Data In Your Graph? Take your Pick: Combine Month, Moving Average, Maximum or Minimum

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