• 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.

Formulas showing as values in charts

bvanscoy678

bvanscoy678

Member
Hello,


My chart is fed by Array Formula which shows a dynamic list in my chart. I have tried changing "" to N/A to show N/A in the formula, but it also ends up as a category. There are places on my X axis even though there are no values, just the formulas. I have tried the formatting to the hidden and empty cell settings to Gaps and Zero, but doesn't make a difference.


my data is formatted: The Date is on the X Axis. The score series is listed as a bar chart, while the Average series is a line.


Date Score Average

1-1-13 70 66


Thanks for any help. Brent
 
If you need the number of categories to be decreased/increased, you'll need to change your dynamic formula to grow/shrink. Only data points can be hidden via N/A, not category names. Look at using the OFFSET function to control how many categories to include.
 
Okay. Just to be clear, I need to insert the Offset into my array formula?


=IFERROR(INDEX(B$2:B$13,SMALL(IF(A$2:A$13=J$1,ROW(A$2:A$13)-ROW(A$2)+1),ROWS(F$3:F3))),"")


thanks for the quick reply. Brent
 
Not quite. So, you've got your array formulas building a dynamic list. Let's say its in col G. Your new named formula to control what gets fed into chart is defined as:

=OFFSET($B$2,0,0,COUNTIF(B:B,"<>")),1)

which should define a range limited in size to what you actually have values for. read this for more info:

http://peltiertech.com/WordPress/dynamic-chart-source-data/
 
Okay, I'll read the link. Funny, I searched his site for answer, but did not know what I was looking for. Thanks.
 
This sounds familiar. I just watched it on my excel school with Chandoo. I'll have to go back and watch it again, I believe he covered this in nice detail.


Again, Thanks
 
Luke,


I can't sure the entire row or column as a reference because of how my worksheet is laid out now. I won't have more than 3 columns, but my list grows down (I add more dates). I tried John's formula also, which is a bit different with earlier verison.


I get the idea, but I get an error on the last ,1)


=OFFSET(AN8:AP8,0,0,countif(AN9:AN20,"<>")),1)


Score Average

Mar-12 2.85 1.70

Jun-12 3.85 1.70

Nov-12 3.28 1.70


thanks
 
Hello,


Still an error with ,3))


My chart is on a different page, so I'll need to include the sheet name along with offset. Just figured that out. I found a link on chandoo about this, but slightly different. I am going to figure out if I can post a example workbook. I am pretty close to getting it, just need to sort through it. Thanks
 
I did some more work on it, but stuck for now. I entered the formula from John Peltier's site, but I have the series jacked up.


here is my sample file to explain a little better. Thanks


https://www.dropbox.com/s/xzfbp53259af6jm/EE%20dyanmic%20chart%20range.xlsm
 
Hi ,


Can you download this file and see if this is what you were looking for ?


http://speedy.sh/6zMGN/EE-dynamic-chart-range.xlsm


Three named ranges have been defined.


Narayan
 
Narayan,


Yes, that is what I am looking to do. Thanks. I can see you created an offset formula for each part separately. One thing that confuses me is when I check the chart's data source, I don't see any reference to the name ranges?


Thank you,

Brent
 
Narayan,


I found the chart source references. I can see that I was looking at the chart data source, not the series. The name ranges uses the rows and counta, so I get it now.


Thanks
 
Last Question,


Do you always been to build a dynamic range for each series or is it possible to build a dynamic range that finds the rows and column together for the data source?


Thanks for the help!
 
Hi ,


Sorry , but I have not understood your question ; can you rephrase this question with specific reference to your workbook , since with the data before us , it will be easier to understand what you want.


In your workbook , I have created 3 dynamic named ranges X-axis , Score and Average ; are you asking whether instead of creating these 3 individual ranges , we could have had just one range consisting of 3 columns , and Excel would have automatically recognised them as consisting of the X-axis and 2 series ?


Narayan
 
Sorry, I'll try to be more clear. I have seen your created 3 dynamic named Ranges (X-Axis, Score and Average). Is it required to create 1 name range for each (X-Axis, Score and Average)? Or can you create one name range that covers the table? I think it is required to have 3 name ranges, but I was curious if it was required to make it work.


Thanks
 
Sorry to be a pest, but I really like to understand what help I have been given, instead of just taking the results and moving on. It helps me learn.


I now see what you are doing. The Rows(X_axis) through me off at first, but basically you are counting the number of rows that have a value in the name range (X_axis)? This way if I decided to another column, I just need to create a name range for it, then refer to the X_Axis again.


X Axis =OFFSET(Data!$F$3,,,COUNT(Data!$F:$F))

Score =OFFSET(Data!$G$3,,,ROWS(X_axis))

Average =OFFSET(Data!$H$3,,,ROWS(X_axis))


Terrific lesson!!! Thank you for being so patience with me. Brent
 
Hi ,


I have made few area charts using dynamic name ranges in Excel 2010. When I right click on the chart and go to "Select Data". for any series collection I write =<workbookname>!<dynamic name range> . It works perfectly fine in Excel 2010. Now when I try to open the file in Excel 2007 , in the chart source I see <0>!<dynamic name range>. I do not see the workbook's name hence getting an error.


Could you please help me what could be the potential way of solving the problem.
 
Hi ,


I have faced the same problem , and have not got any solution ; I think the problem may not happen if you use the template sheetname!named_range.


Narayan
 
Hi Narayan,


Thank you for replying back. So if I use the template that you suggested. Which Sheet's name should it be , the sheet which has the namerange's data or the sheet which has the chart.
 
Hi ,


The sheet name will be the sheet on which the data is ; since you will use the named range instead of the actual range reference , use the relevant sheet name ; thus if your data range would have been J7:J77 on a sheet named Test Sheet , and the named range is Dynamic_Data_Range , then the chart series would be ='Test Sheet'!Dynamic_Data_Range ; if you had used the actual range reference , you would have used ='Test Sheet'!$J$7:$J$77


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