+ Reply to Thread
Results 1 to 8 of 8

Dynamic chart with Offset not working? Volitale??

  1. #1
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Dynamic chart with Offset not working? Volitale??

    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!!!

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,547

    Re: Dynamic chart with Offset not working? Volitale??

    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
    Ben Van Johnson

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic chart with Offset not working? Volitale??

    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))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  4. #4
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Dynamic chart with Offset not working? Volitale??

    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!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Dynamic chart with Offset not working? Volitale??

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

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic chart with Offset not working? Volitale??

    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))

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Dynamic chart with Offset not working? Volitale??

    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

  8. #8
    Registered User
    Join Date
    08-08-2010
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2010
    Posts
    85

    Re: Dynamic chart with Offset not working? Volitale??

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1