Exclude #N/A values from stacked column chart

[rg144]

Hi guys,

Im trying to create a stacked column chart that only shows rows with values and excludes rows with #N/A values.

NOTES:
1. I can't filter as I have 2 columns I wish to display in the stacked chart, and sometimes a #N/A value in column B does not equal #N/A in column C..
2. I can't hide rows as each cell is dynamic (values are generated using COUNTIFS formulas).


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Expiry_Date[/TD]
[TD]Transport_By_Ship[/TD]
[TD]Transport_By_Air[/TD]
[/TR]
[TR]
[TD]19/07/2012[/TD]
[TD]#N/A[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]20/07/2012[/TD]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]21/07/2012[/TD]
[TD]#N/A[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]22/07/2012[/TD]
[TD]#N/A[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]23/07/2012[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]24/07/2012[/TD]
[TD]2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]25/07/2012[/TD]
[TD]1[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]26/07/2012[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]27/07/2012[/TD]
[TD]1[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]28/07/2012[/TD]
[TD]#N/A[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]29/07/2012[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

In the example above- currently my stacked column graph charts all days.

In the example above- I don't want the bolded rows (23/07 and 26/07) to be charted (meaning i want to remove the empty space in the outputted chart)

I've searched the net tirelessly and cannot find an answer

Any help would be much appreciated guys.

Kind Regards.

 

[rg144]

For reference/to help everyone understand, here is my excel (2007) workbook: http://www2.zshare.ma/3jucbzpjff40

Thanks guys.

 

[Skybluekid]

Try putting =IFERROR(IF(COUNTIFS(DATA!B:B,CHART!A2,DATA!C:C,CHART!$B$1,DATA!A:A,$F$5)=0,NA(),COUNTIFS(DATA!B:B,CHART!A2,DATA!C:C,CHART!$B$1,DATA!A:A,$F$5)),"")

 

[CAPtain232]

The iferror function will only make a cell read whatever you want it to read, but it will not delete the row all together....I would assume, as I am still learning myself, that this would have to be done with code and not a simple formula. Hopefully someone will chime in.

 

[rg144]

Try putting =IFERROR(IF(COUNTIFS(DATA!B:B,CHART!A2,DATA!C:C,CHART!$B$1,DATA!A:A,$F$5)=0,NA(),COUNTIFS(DATA!B:B,CHART!A2,DATA!C:C,CHART!$B$1,DATA!A:A,$F$5)),"")

Skyblue, thanks for your prompt reply.

I tried that previously, it does remove #N/A values from the cells and 'clears' them (if there's an excel error), however I still have blanks (gaps) in my chart as shown below..




basically I want the x-axis (date) to dynamically (automatically) remove dates that have no values.. i hope im making sense

 

[Skybluekid]

In F7 =IFERROR(INDEX($A$2:$A$40,AGGREGATE(15,6,(ROW($A$2:$A$40)-ROW($A$2)+1/($B$2:$B$40>0)*($
C$2:$C$40>0)),ROWS(G$46:G46))),"") and drag down to F47 -- This will give you the dates with no gaps

In G7 =IFERROR(INDEX($B$2:$B$40,MATCH(F7,$A$2:$A$40,0)),"") drag down to G47
In H7 =IFERROR(INDEX($C$2:$C$40,MATCH(F7,$A$2:$A$40,0)),"") drag down to H47

Create your chart from F7 to H47