Ignore Blank Cells while sorting on bar/column chart in Xcelsius 2008 SP4

[Former Member]

Hi,

I have selected ignore blank cells option on barchart and it is working fine. When I tried apply sorting option, it started showing the blank values.

is this the expected behavior or bug? do any one have work around for the same? Appreciate your help.

[Former Member]

Hi Rama,

As Sara said, Ignore Blank only works when the blanks are at the end of the series.

You could instead merge your data manually.  A simple example: if your data is in A1:A10, then set

B1 = LARGE($A$1:$A$10,1)

B2 = LARGE($A$1:$A$10,2)

etc

Then map the B1:B10 values to your bar chart.

[former_member184839]

Hi Rama,

A bar Chart would ignore the blank cells and the blank series , if and only if they are at the end of the range. Sorting will not affect the blank cells anyway. Make sure your blank cells are at the end of the range.

If it doesn't work then try, remapping the data to your chart,sort it then check the ignore the blank cells option.

Thanks,

Sara G

[Former Member]

Thanks Sara for your help.

As I mentioned earlier, Ignore blank cell option is working fine when I don't apply sorting.

Following is the sample data set.

-----------------------------------------

City           Revenue%

Houston     80%

Austin        90%

Dallas        0%

Katy          70%

Chicago     <blank>

New York   <blank> 

---------------------------------------

if i apply sorting option, the blank cell is coming before the zero like below. Hence the ignore blank cell option is not working.

-----------------------------------------

City           Revenue%

Austin        90%

Houston     80%

Katy          70%

Chicago     <blank>

New York   <blank> 

Dallas        0%

---------------------------------------

do thing any option other than filtering blank cells in excel.

Regards,

Rama

[former_member184839]

Hi Rama,

Actually in the second case,

      B                    C

      City           Revenue%

1.  Austin        90%

2.  Houston     80%

3.  Katy          70%

4.  Chicago     <blank>

5.  New York   <blank> 

6.  Dallas        0%

Your blank cells are coming in between(which is not at the end of the series), That's the reason it isn't ignoring the blank cells.

There are workarounds for it , One way of doing it is,

You ll have to write excel formula and do it.

Have one column before your data. Suppose column A.

In the cell A1= if(C1="","",1)

This ll give you 1, wherever there is data in cells of column C.

Now, Have a hidden combo box. Your labels ll be the A column in this case (A1:A6)

Source : (B1:C6)

Destination : (E1:F6)

In the Behavior tab , in "Selected Item" Give it as, Label, and ,map it to the cell which has "1" in it.

Now, E1 to F6 is your final data, You can do sorting there.

Thanks,

Sara G