• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Labeling incremental amounts within each total bar/column

C

Chartastic

New Member
OK, so this is a bit difficult to describe, but easy to understand when seen. The best bet is to look at the file with the hand drawn chart example inserted as a picture, but here goes...
NOTE: As this is intended for Mac users, we're locked to Office 2011 (not 2013) for now.
Basically, it is a multiple column/bar chart in a timeline-based X-axis; however, the real trick I am trying to accomplish is getting incremental labels to show the individual data points as they are "stacked" to make the total/whole column or bar. IOW, I'm trying to get my otherwise simple (total) column/bar to label the individual data points that make-up the whole column/bar, such as labeling data point A @ position A; data point B @ position A+B; data point C @ position A+B+C and so on in any/each column or bar. The best I can do is label the grand total of each, but the segmented aspect seems much more complex than it would appear from the mockup drawing.
We want to be able to enter the date, category, description and amount (each row) and have the chart update automatically (the label column is actually formulaic and would be hidden with a narrow cell width and/or fill, but it gets the label text as pictured). The data will be sparse, but we understand there may be label layout and overlap issues if data falls too close together in amount and time. The real roadblock issue is the concept of labeling the increments of a total column/bar chart. Again, I hope that makes sense in writing, but it should be perfectly clear, visually, via the sample file.
Many thanks for any solutions, ideas or even confirmation that this is a bit crazier than it looks (barring VBA expertise?)!
 

Attachments

  • StackingAmounts.xlsx
    88.9 KB · Views: 19
Last edited:
Hi ,

Before we decide how to solve your problem , can you take a look at the uploaded file , and say whether the chart is anything like what you are looking for ?

Narayan
 

Attachments

  • StackingAmounts.xlsx
    21.8 KB · Views: 19
The original example is simplified in order to understand how to do this at all; however, the attached is another version I have been working on, which shows the drawing of different columns (not bars, apparently b/c it's vertical, although that gets me confused between the original columns, as in rows & columns, but I digress...) for each category as well as for each month. I also like the monthly timeline view.
I just can't figure out how to put incremental labels/points on this either.
 

Attachments

  • CharityTimeline.xlsx
    718 KB · Views: 10
It's close, but look at my first sample to see the idea of the "stacking" of labels, such that two donations to the same charity would show one total column; however, the first donation amount would be labeled at its relevant amount on the Y-axis (aka, amount A). The second donation amount label would be placed at the top (the first donation amount pus the second donation amount, or amount A+B), but be labeled not with the grand total (A+B), but its own amount (amount B). Any subsequent donation amounts would "stack" on top of the last amount (in time order for that month), such that a new, third point for amount C *if it occurred later in time* would be located at A+B+C (the "new top", leaving B somewhere between A & C), but labeled as amount C and so on. If this new third donation occurred in time before either B or before A, then it would situate in its appropriate location in time, possibly becoming A or B and moving the original A and/or B "up" to become B and/or C.
Your chart is only showing grand totals for each charity all in one column per month. I was able to come closer with the second sample, showing discrete columns for each charity, but only grand totals and not the incremental labels that make-up same.

So, look at my second sample and notice the fact that there can be multiple columns each month (one for each charity). Any/each charity bar column should be discrete in any given month, while each bar would show only its own incremental labels as well.

The holy grail is a discrete column per charity (per month - up to four in my two samples), but also incremental labels on each of those charity-specific columns.

Does that help to clarify?
 
Last edited:
Hi ,

Let me understand ; suppose all 4 charities donate in the month of September , the amounts being 100 , 200 , 300 and 400 ; please clarify the following :

1. Do you want the bars to overlap and be shown one on top of the other , or be shown side by side ?

2. Do you want the labels for each bar to be the individual values viz. 100 , 200 , 300 and 400 ?

Narayan
 
Ah, I see the subtle difference now (sorry, it's already tomorrow here) between #7 and #9 charts!

The labeling is closer in terms of incrementing, but within a single bar column for any/all charities in a month (OK, yes, I see that the coloring shows it is two columns, but they are overlapping - sorry for my confusion). If you can separate Charity D from Charity C in October, so there are two columns for October, that's the key. Also, the single column for Charity A in September should have two labels - one at $50 and the other saying $100 at the $150 level of the Y-axis...

To answer post #11:
1) The bars columns should be side by side, which may be better visualized with my second sample chart.
2) The labels should appear on the chart itself at the proper "total" (in time) Y-axis value, but the text would be the amount. As per above, the Charity A column in September should show two labels. One that shows at the $50 level and says $50, but the other that shows at the $150 level, but says $100 (or, as per the label data from both examples "Amount+<newline+aka CHAR(10)>+<Description>") and so on...
 
Hi ,

Either we consider the example I have given , or we consider the data in the file you have uploaded ; doing neither will leave both of us confused !

You mention , in your latest post , 50 and 100 ; where are these two amounts in your data ? Your Annual tab shows only one amount of 150.

Anyway , as I have understood it :

1. You want columns side by side.

2. The height of the columns should be as if they are stacked one on top of the other

3. The labels should be of the individual values

All I can say is it is going to be thoroughly confusing ; to take the example figures that I had mentioned , of 100 , 200 , 300 and 400 in one month , the first column will touch 100 , the second will touch 300 ( 100 + 200 ) but show a data label of 200 , the third will touch 600 but show a data label of 300 , and the last will touch 1000 but show a data label of 400 !

Anyway , if that is what you want , it's OK. I'll upload a file which has that.

Narayan
 
In my first sample, look at cells D2 and D3 and the whole rows, and look at the first, green (September) line in the mockup drawing in same.
In my second sample, look at the "September" sheet.
On the "Annual" sheet, look at the formulae used to get those totals you mention - they are based on the above data points, which is how $150 is made from the $50 and $100 I keep mentioning re: September and Charity A.

There are two distinct amounts donated to Charity A in September. Your bar/column is showing the total for September but not also the two points (labels) within same.

Otherwise:
1) Yes, side by side (I just glanced at and see that in your latest chart in post #15, but September is still a single total without its two points that makeup that total being shown/labeled).
2) The height of each column is the total of any donations for each charity in each month, so it is the individual row amount that are stacked - if they are from the given charity (not all) and if they are from the same month.
3) Yes, there may be label issues, but if you look at my second sample, I have the totals and multiple bars per month (per charity, if any) working. You may need to throw in more data points for more months to see the multiple columns per charity per month better, but the gist is to get the labeling correct for multiple donations to the same charity in any given month as described. If there are labeling issues, it is unlikely to matter, since the data is sparse, as per the first sample provided. Even so, in that event, there may need to be unfortunate label movement, manually, since Excel cannot understand to make one label left and one label right to avoid overlap on the fly...
;)

I apologize if this is an exasperating question - trust me, I can completely understand, as I like to think I am pretty savvy, but this chart has been the bane of my existence for weeks and knowing how complex it may be is validating my efforts as well...
Many thanks for your help on this! :)
 
I took a closer look at your latest example, and it works very similar to my second sample. So, the real trick is to get the individual points that make-up those total bars to show (as labels in some way). Similar effort is what I saw in the waterfall chart example on this site. It seems to be one chart to the naked eye, but is several with lots of hiding and adding of elements to make what seems like one fancy chart with a total that is broken down into its elements. It seemed that this chart I am working on might be something like a collapsed version of that waterfall one (but also multiple - very complex).

Looking at the original sample, this is deceptively simple, but devilishly complex. I think we have found one of those examples, where humans comprehend and do something with ease, but getting a computer to do so is much more difficult, if possible - like facial recognition!
:DD
 
Last edited:
Hi ,

The simple fact is there was no mention of where the chart was to be built from. The Annual sheet becomes irrelevant then , since the data has to come from the individual tabs.

If the chart had not been on the Annual tab , or if it had clearly been mentioned that the data is to be taken from the individual tabs , some time could have been saved.

Narayan
 
Fair enough and my apologies for not telling you to look at *all* of the sheets in the second sample.
As I mention, I understand your frustration, as even in the very first sample, with the mockup drawing, we'd still have this original issue with Charity A and the $50 + $100 in a $150 column chart on that single sheeted workbook.

The second sample just shows that there is still the issue of multiple bars per month if more than one charity gets a donation in the same month, which was obviously not clear in the first sample.

The goal has always been stated as:
"I'm trying to get my otherwise simple (total) column/bar to label the individual data points that make-up the whole column/bar, such as labeling data point A @ position A; data point B @ position A+B; data point C @ position A+B+C and so on in any/each column or bar. The best I can do is label the grand total of each, but the segmented aspect seems much more complex than it would appear from the mockup drawing."
That's true even when there are multiple charities (bars) per month.
As I mentioned in my very first post, this is much more difficult to explain than to see, but even then, the deceptive simplicity of both charts also apparently is misleading!
;)
 
Last edited:
I have never seen that article before (partly b/c I got sidetracked by the idea of the segments of a waterfall chart on this site and the possibility of the idea of a "collapsed waterfall" chart that I thought might exist), nor would I have given my various search terms used to date.

I will definitely look at that and keep "stacked" and "clustered" (and "Cluster Stack") in mind.
 
Last edited:
Hi ,

If the chart can be developed , putting in the data labels can be done using the macro ; this will not be a problem.

The first step is to see if the chart can be created.

Are you going to give it a try , or should I do it ?

Narayan
 
If you're curious if it'll work in this case, you might give it a whirl; otherwise, it could take me a while to look at it, understand it, and then try to get it to work.

I don't want to be obnoxious and throw all the work on you, but if you think you can find out if it will work, I'd be very appreciative of the effort, and say go for it (thanks)!

I still have to understand it all and will have to work through it even if you can get it to work (or confirm it won't) before I get there myself.
 
You must log in or register to reply here.
Back
Top