+ Reply to Thread
Results 1 to 7 of 7

Dynamic Chart / Pivot Chart - a Bear's Problem

  1. #1
    Registered User
    Join Date
    10-10-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Post Dynamic Chart / Pivot Chart - a Bear's Problem

    Hi All,

    I am having the following issue, here is the example excel file:

    Two friends Bear and Mountain love collecting rocks and regularly go out in the field and have been doing so for years. Every time after collecting the rocks they sort them into size classes (small, medium, large) and into colors (brown, white). Next, they split each size and color combination up into four bags. Two bags go to Bear and two go to Mountain and they try to make a 51/49 split by weight. So two bags of white small rocks go to Bear and two go to Mountain, Bear gets ~51% by weight, Mountain gets ~49%. And they do that with each size class and color combination. White - small, white - medium, white - large, brown - small, brown - medium, brown - large.

    After a few years Bear feels like he might be getting a little bit less than the agreed upon 51% by weight, especially in the brown - large stones, his favorite ones! So he decides to do put it all into an excel spread sheet to find out if the split has been fair over the years, and if there are changes between colors or sizes.

    After trying things out for a while, he discovers that he can use a pivot table and chart (see file) to display the percentage for each size class, and he can even use slicers to calculate the split for only select collection sessions, or only select size classes.

    The problem he is having, is that he can only show the percentage that Bear and Mountain received, but he would rather show the deviation from the split.

    !! So, instead of showing that Bear received for example 50.5% and Mountain 49.5% of the large brown stones, he would rather have the graph show -0.5% as this is the deviation of the 51/49 split. !!

    Does anyone know how to do this?

    Please let me know if you have any questions!

    Cheers

    Bear
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-10-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    Hi everyone,

    Does anyone have an idea? It does not necessarily have to be a Pivot table solution...

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    Hello Coyotarra and Welcome to Excel Forum.
    See if the chart in the attached copy of the file is close to what you want.
    1) L43:M49 are populated using: =IF(H43=0,"",H43)
    2) N44:N49 are populated using: =IF(J44="","",J44-0.51)
    3) O44:O49 are populated using: =IF(K44="","",K44-0.49)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    10-10-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    Hi JeteMc,

    Yes, what you did is indeed close to what I intended! Thanks for the effort!

    There is just one more question for your solution. The chart does everything I want, but it draws its data from a fixed range: L44:M49. Therefore, if I only select one Size and one Color, most of the chart is empty - see the attached excel file. Usually this is not a such a big problem, but the real data I am working with is a lot more complex (lets say there are a lot more colors and sizes to choose from ). Is there a solution that the chart will only every show filled cells/selected attributes?

    Thanks for your help

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    The attached file comes close to doing what you want. The chart is fed by named ranges which are dynamic per use of OFFSET as described in this article. Scroll down about halfway to the section heading: 'Using Excel Formulas'.
    For some reason when only one color and one size is chosen the size doesn't appear. I will ask some of the other contributors if they have a solution that will resolve that issue.
    Let us know if you have any questions.

  6. #6
    Registered User
    Join Date
    10-10-2018
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    4

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    That is a great solution, thank you a thousand times!

    The issue you mentioned would be nice to solve, but the chart works nonetheless.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Dynamic Chart / Pivot Chart - a Bear's Problem

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Pivot Chart Radar Chart Labels Problem
    By jancliff in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-12-2021, 12:11 AM
  2. Chart order in trad. Pivot Chart vs. PowerPivot chart
    By mr_jules in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-06-2015, 11:17 AM
  3. How to resize a dynamic pivot chart
    By Alyena in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 07-03-2014, 02:55 AM
  4. Dynamic Chart problem
    By jtd84 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-04-2010, 09:55 PM
  5. Problem with dynamic chart
    By cdeford in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-18-2009, 12:22 PM
  6. Dynamic Pivot Chart based on Pivot Table.
    By excelkeechak in forum Excel General
    Replies: 3
    Last Post: 12-01-2009, 09:23 PM
  7. Dynamic chart problem
    By akullen in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-13-2008, 03:58 AM

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