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
Bookmarks