Results 1 to 4 of 4

Thread: Dynamic Chart help Excel 2007
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2012
    Location
    Edmond, OK
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Chart help Excel 2007

    Need help taking the last data point (DCP) off several dynamic linear charts. The last data point is the weekly average and it often creates a blip at the end of the data. No VBA please. Thank you so much.

    =OFFSET(Current!$A$1,25,MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0),1,MATCH(Current!$A$174,Current!$B$1:Current! $XEA$1,0)-MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0))

    A DCI DCJ DCK DCL DCM DCN DCO DCP
    8/21/2019 8/22/2019 8/23/2019 8/24/2019 8/25/2019 8/26/2019 8/27/2019 Weekly Avg
    3 3 19.0 19.1 18.8 19.5 19.5 19.6 19.5 19.3
    4 4 19.3 19.5 19.7 19.7 19.9 20.2 20.3 19.8
    5 21 20.6 21.0 21.0 20.9 21.5 21.6 21.8 21.2
    6 22 20.3 20.1 20.3 20.4 20.5 20.5 20.8 20.4
    7 22.3 22.3 22.3 22.3 22.3 22.4 22.6 22.4 22.4
    8 20.7 20.7 20.6 20.8 20.7 20.6 20.6 20.3 20.6
    9 20.8 20.8 20.8 21.0 21.2 21.3 21.5 21.5 21.2
    10 21.8 21.8 22.2 22.0 21.9 22.0 22.3 22.4 22.1
    11 20.8 20.8 20.5 20.4 20.3 20.9 21.1 21.2 20.7
    12 19.6 19.6 19.8 19.8 19.9 19.7 19.8 20.0 19.8
    13 24.0 24.0 23.8 23.6 23.5 23.4 23.6 23.9 23.7
    14 25.4 25.4 25.2 25.0 24.9 24.8 25.3 25.3 25.1
    26 20.8 20.8 20.9 20.9 20.9 21.1 21.2 21.3 21.0

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dynamic Chart help Excel 2007

    I believe this will do it:

    =OFFSET(Current!$A$1,25,MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0),1,MATCH(Current!$A$174,Current!$B$1:Current! $XEA$1,0)-MATCH(Current!$A$172,Current!$B$1:Current!$XEA$1,0)-1)

    The -1 in the location shown will remove 1 from the width of the block specified by the original OFFSET formula.


    I am assuming that Current!$A$174 contains "Weekly Average" instead of the final date of the week and A172 contains he fir date of the week.
    Last edited by pbornemeier; Oct 2nd, 2019 at 09:38 AM.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

  3. #3
    New Member
    Join Date
    Jul 2012
    Location
    Edmond, OK
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Chart help Excel 2007

    Thank you for responding ....I sent you a PM.

  4. #4
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,659
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Dynamic Chart help Excel 2007

    If the Weekly Average column (DCP) is left in and the next week's data starts in column DCQ then I don't see a simple way to show (or compare several months of that data without the weekly average Blip unless you copy the data, remove the weekly average columns and plot the "cleaned" datasets. This could be done manually, or with VBA.

    Something I just tried (in Excel 2016) was to delete the text titles (Weekly Avg) and leave the average values in the range I was plotting. Excel skipped those points and only plotted the columns with dates at the top. I don't know if earlier versions of excel do this.
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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