JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
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.
How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
[TABLE="width: 1166"]
[TABLE="width: 1166"]
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!
I have excel 2000 and =na() works fine - I think about it
found this by googling[FONT="]Have you checked the "Hidden and empty cells" settings in your excel chart ?[/FONT] [FONT="]It should be set to "Gaps".[/FONT] [FONT="]- Click on the boundary of your excel chart[/FONT] [FONT="]- Click on "Select Data" under "Chart Tools >> DESIGN " [/FONT] [FONT="]- At the bottom left corner of the newly populated box, you will see a button titled "Hidden and empty cells"[/FONT] [FONT="]- Click that button[/FONT] [FONT="]Change the settings. Your problem should get solved.[/FONT]
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
Actually my problem is that I get the #N/As as labels on the x-axis, right next to the quarters.
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 a moderator: Feb 20, 2018
Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2
