How to make dynamic charts in Excel by using non-blank values?

[comeacross]

Hi all,

Could you help me with a pie chart problem?

I have two input ranges, say A1:A50 and B1:B50.

The cells in these ranges contain formulas, that will either return a value (country name for column A and percentage for column B) or a blank (" ").

The pie chart shows all the blanks as 0. I want the pie chart to only show the non-blanks. If A is non-blank, B is automatically also non-blank.

This is the formula that I used:


=IF(COUNT($K$61:$K$127)>=ROWS(K$61:K61);INDEX($B$61:$B$127;MATCH(IF(LEN($K$61:$K$127)>0;ROW($K$61:$K$127);0);"")))

Then the results turned to be "#VALUE!". Any ideas about how to solve this problem?

Thanks in advance for your help!




　

 

[comeacross]

Hi all,

Sorry for the last post which I did not describe my questions clearly. So I make it more clear in this post. Thank you so much for your help.

 

[comeacross]

Hi, dear all. Is anyone there have some ideas or hints about how to solve this problem. I am so appreciate for your help. Wish you have a nice day. Thank you so much.