Data table showing 0% when cell is empty

[Diddy]

Hi,


I'm using data tables in a master doc that picks up data from various data sheets. Data is brought through using an index match formula. Data is on schools and when the school number is changed on the master sheet data for that school is brought in. This works well for larger schools but for smaller ones there may be no pupils matching the criteria that is being measured.


The problem is that on the data table for charts I get 0% even when the cell returns a blank. Cell formula is =INDEX(_201105_Sci_FSM_Pcent,MATCH($AQ$1,_201105FSMEstab,0))The cell this pulls data from is blank and when looked at with John Walkenbach's data map PUP v6 utility is a text cell.


Can the data table display a blank?


Any idea how to get around?


many thanks

Diddy

 

[Hui]

Diddy


Firstly, Welcome to the Chandoo.org Forums


Change the cells format to a Custom Number Format

Ctrl 1,

Custom, Number

Enter 0%;-0%;

in the custom format box and apply

 

[Diddy]

Hi Hui,


Thank you for replying and the welcome .


The solution doesn't totally work but is if I were to have something like "no pupils" in the lookup cell; could I use custom formatting to blank it?

Problem is now that 0% does not show when source cell is blank but also does not show where the result is actually 0% and should show in the data table.


many thanks

Diddy

 

[OleMiss2010]

Might it work to put an if statement similar to =if(target cell is null,"",INDEX). That's a very rough idea of what might work, but I think the intent is clear.