• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic chart - How to make the chart avoid cells that are empty but w/ formula

C

cacos

Member
Hi everyone!


I'm struggling in creating a chart, where I have an horizontal set of values, and I'm having the chart work dynamically by naming ranges and populating with this formula "=OFFSET(eNPS!$J$31,0,1,1,COUNTA(eNPS!$31:$31)-1)"


This way, the series should only pick up cells that have values. The problem is that some cells have no value, but there's a formula resulting in "", thus "populating" and the COUNTA picks them up.


Any idea how I could avoid the chart from picking up this blank cells with formulas?
 
I'd recommend either changing the COUNTA to just COUNT if you're looking at numerical values, or you could change it to a COUNTIF like this:

=OFFSET(eNPS!$J$31,0,1,1,COUNTIF(eNPS!$31:$31,"<>")-1)
 
Hi Luke M, thank you for your help. I'm trying it yet it doesn't work, i'm not sure i'm using the right formulas.


On the series there's names, and the values are numbers, placed horizontally. E.g. on row "31" James,John,Jack, and on row "32",1, 5,20. The issue is that due to formulas changing, John could turn into a "" along with its value, number 5. I'm trying to make the chart avoid that cell, instead of showing a blank series.


Thank you again!
 
Hmm. How about this then?

=OFFSET(eNPS!$J$31,0,1,1,COLUMNS(eNPS!$31:$31)-COUNTBLANK(eNPS!$31:$31)-1)
 
Cacos. Do this.

=if(OFFSET(eNPS!$J$31,0,1,1,COUNTA(eNPS!$31:$31)-1)=0,#N/A,OFFSET(eNPS!$J$31,0,1,1,COUNTA(eNPS!$31:$31)-1))


#N/A allows blank cells to not display on graphs and wont mess up the series. Also instead of using COUNTA use Count
 
Hi ,


Is this what you are looking for ?


https://docs.google.com/open?id=0B0KMpuzr3MTVYldkYi04WUtQQTg


Download the file , open it in Excel , and change the numbers in row 18 to 0 and see that the chart changes.


Narayan
 
Hi! I've seen the document and series are still shown in the chart (though they appear blank).


I was trying to make the chart avoid them all in all.


Thank you for your help :)
 
Cacos

Have a look at the formula technique used here:

http://chandoo.org/wp/2012/03/01/formula-forensic-014/
 
Hi ,


I am unable to understand your problem ; as I understood you , you had a series , where some points could be "" because of a combination of data and formulae. You did not wish to plot these 'blank' data points. Is this correct ?


This is what has been done in the uploaded file ; there is a series of 10 data points ; depending on the values in row 18 , if any value is made 0 , the corresponding point disappears from the series and is not plotted.


If this is not what you wanted , can you explain your requirement , or better still upload your file with the existing data and chart ? Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


Narayan
 
You must log in or register to reply here.
Back
Top