Dynamic chart with Offset not working? Volitale??

**trillium** · 05-12-2013, 10:10 PM

Hi
I am trying to follow this example where I use the Offset function to build a dynamic chart, however I can't even get past the OFFSET funtion!

It keeps returning a #VALUE! error and when I look at the function the result is volatile.

The data range I am referring to is not updated by a user, but by a formula range that is referring to another place that is being updated and will either get bigger or smaller depending on what criteria the user is entering on the input page.

I based by formulas on this example that I had seen in a few places, including here: http://chandoo.org/wp/2009/10/15/dyn...t-data-series/

However I used Countif instead of CountaA because my first column is names and even when it is not showing "data" the cells have formulas that refer somewhere else. (if they meet the criteria, they show the result, otherwise it's "").

Even when I just try a regular OFFSET formula I get a volatile result??

What am I doing wrong? I am using Excel 2010.

I'm so frustrated!!!

**protonLeah** · 05-12-2013, 10:20 PM

To Attach a Workbook:
1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit

**martindwilson** · 05-12-2013, 10:27 PM

youll always see volatile with offset there is another way to make a dynamic range using index but countif based on what?
you could use
=SUMPRODUCT(--(LEN(A1:A10000)>0))

**trillium** · 05-12-2013, 10:55 PM

Man, so when I created a sample of the workbook, the offset formulas suddenly worked and I could even get the chart to generate. But as soon as I update it, it went back to value error again.

The place where I was trying to test the offset formula is on the SourcePivots tab, N79 (you will see it highlighted in yellow).

To change the pivot charts, select either Jane Doe with Renewal or John Doe / Quotes. Leave the rest of the filters as is.

Thanks for your help!

**trillium** · 05-12-2013, 10:57 PM

Hi! I'm not familiar with using the LEN function, what should go where the hyphens are? Thanks!

**martindwilson** · 05-13-2013, 05:25 AM

nothing see http://www.xldynamic.com/source/xld.SUMPRODUCT.html

=SUMPRODUCT(--(LEN(A1:A10000)>0)) simply counts the used cells but ignores "" in range a1:a1000 since you have excel 2010 you could use the whole range =SUMPRODUCT(--(LEN(A:A)>0))

**martindwilson** · 05-13-2013, 05:43 AM

also from your example
=OFFSET(SourcePivots!$P$81,0,0,(COUNTIF(SourcePivots!$P$81:P105,">=0")),1) shows value which is correct behavior
you can check its working by
=SUM(OFFSET(SourcePivots!$P$81,0,0,(COUNTIF(SourcePivots!$P$81:P105,">=0")),1)) which gives 13
so there is no reason you cant use the
=OFFSET(SourcePivots!$Q$81,0,0,(COUNTIF(SourcePivots!$P$81:P105,">=0")),1) as your data source

**trillium** · 05-13-2013, 12:16 PM

Well I still get volatile results with the offset function when trying it in my real life example!?!?

The LEN idea is a good one, but it counts the blank cells becuase they have formulas in them... so it thinks it's non-blank...