Results 1 to 9 of 9

Thread: Months/Quarters Dynamic Chart

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Months/Quarters Dynamic Chart

    Dear All,

    I am working on an Excel dashboard. One of the requirements is to display monthly and quaterly data on a single graph depending on user's selection. If the user wants to see volumes per months, the graph should present monthly data. If the user chooses to see the quaterly data, the graph should show quarterly volumes. I tried with pivot charts and slicers but it doesn't work the way as I would like it to be. Do you have any ideas how I could implement this requirement? I really appreciate your help.

    Many thanks,
    S.

  2. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,339
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    date amount month quarter
    01/01/2017 32 Jan 1
    18/01/2017 35 Jan 1 apr 2
    04/02/2017 38 Feb 1 aug 3
    21/02/2017 41 Feb 1 dec 4
    10/03/2017 44 Mar 1 feb 1
    27/03/2017 47 Mar 1 jan 1
    13/04/2017 50 Apr 2 jul 3
    30/04/2017 53 Apr 2 jun 2
    17/05/2017 56 May 2 mar 1
    03/06/2017 59 Jun 2 may 2
    20/06/2017 62 Jun 2 nov 4
    07/07/2017 65 Jul 3 oct 4
    24/07/2017 68 Jul 3 sep 3
    10/08/2017 71 Aug 3
    27/08/2017 74 Aug 3
    13/09/2017 77 Sep 3 month total quarter total
    30/09/2017 80 Sep 3 jan 67 1 237
    17/10/2017 83 Oct 4 feb 79 2 280
    03/11/2017 86 Nov 4 mar 91 3 435
    20/11/2017 89 Nov 4 apr 103 4 445
    07/12/2017 92 Dec 4 may 56
    24/12/2017 95 Dec 4 jun 121
    july 0
    aug 145
    col D col E sep 157
    oct 83
    which chart type quarter nov 175
    dec 187
    1 237 row 31
    2 280 the helper tables above can be hidden away
    3 435
    4 445 depending on choice this table
    #N/A will be 4 numbers and 8 #N/A's
    #N/A (which will not plot on a chart)
    #N/A or 12 numbers
    #N/A
    #N/A
    #N/A base the chart on this table D31:E42
    #N/A
    #N/A

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,339
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    date amount month quarter
    01/01/2017 32 Jan 1
    18/01/2017 35 Jan 1 apr 2
    04/02/2017 38 Feb 1 aug 3
    21/02/2017 41 Feb 1 dec 4
    10/03/2017 44 Mar 1 feb 1
    27/03/2017 47 Mar 1 jan 1
    13/04/2017 50 Apr 2 jul 3
    30/04/2017 53 Apr 2 jun 2
    17/05/2017 56 May 2 mar 1
    03/06/2017 59 Jun 2 may 2
    20/06/2017 62 Jun 2 nov 4
    07/07/2017 65 Jul 3 oct 4
    24/07/2017 68 Jul 3 sep 3
    10/08/2017 71 Aug 3
    27/08/2017 74 Aug 3
    13/09/2017 77 Sep 3 month total quarter total
    30/09/2017 80 Sep 3 jan 67 1 237
    17/10/2017 83 Oct 4 feb 79 2 280
    03/11/2017 86 Nov 4 mar 91 3 435
    20/11/2017 89 Nov 4 apr 103 4 445
    07/12/2017 92 Dec 4 may 56
    24/12/2017 95 Dec 4 jun 121
    jul 133
    aug 145
    col D col E sep 157
    oct 83
    which chart type month nov 175
    dec 187
    1 67 row 31
    2 79 the helper tables above can be hidden away
    3 91
    4 103 depending on choice this table
    5 56 will be 4 numbers and 8 #N/A's
    6 121 (which will not plot on a chart)
    7 133 or 12 numbers
    8 145
    9 157
    10 83 base the chart on this table D31:E42
    11 175
    12 187

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    Many thanks for your help!
    Just one more question, I am having a problem as the #NAs plot on the chart, despite having NA() function in the formula. I use excel 2016 and somehow putting NA() function doesn't work.
    I'd appreciate a piece of advice on this.

    Thank you!

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,339
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    I have excel 2000 and =na() works fine - I think about it

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    10,339
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    found this by googling

    Have you checked the "Hidden and empty cells" settings in your excel chart ?
    It should be set to "Gaps".
    - Click on the boundary of your excel chart
    - Click on "Select Data" under "Chart Tools >> DESIGN "
    - At the bottom left corner of the newly populated box, you will see a button titled "Hidden and empty cells"
    - Click that button
    Change the settings. Your problem should get solved.

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    Yes, I did go through all the options you described and they worked well in the older excel versions that I used but seems they don't work for excel 2016

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    Actually my problem is that I get the #N/As as labels on the x-axis, right next to the quarters.

  9. #9
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Months/Quarters Dynamic Chart

    I put in the formula that in case of an error it should display a blank. However, the quarter bars look a bit strange on the graph - they are not evenly distributed as I'd imagine. I don't know if this can be overcome somehow.

    https://ibb.co/hpFXoH
    Last edited by Fluff; Feb 20th, 2018 at 02:41 PM.

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
  •