WizardBrews
New Member
- Joined
- Jan 29, 2018
- Messages
- 5
I have made a budget sheet on excel and I have a few columns per month. Category, Budget, Actual, Remaining. My question pertains to the Budget, Actual, And Remaining sections. Currently I have an Amount X in the budget column that remains constant. In the Actual column I have a formula that grabs amounts from underneath the budget section and puts it into the correct category and adds up all the amounts in that category. The Remaining section, I simply subtract the two. However I use data bars to show amount remaining as well to add some flair. But these data bars are kind of aesthetically useless because they are based on relative amounts currently. So my food budget, which is higher than my Misc budget is massive while the Misc is very small. I was wondering if there was a way that I could get the Bars to actually show the percentage Left of the budget in that category, while also still showing the dollar amount I have left in text. This would make all the bars even because there is 100% left but the text on the bar would say the dollar amount left. I hope this makes sense. Would appreciate advice on this.
I am essentially asking if you can have the length of the bars to be driven by the percentage, but the label on the bars to be driven by the absolute value?
Here is an example of my data set.
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Category[/TD]
[TD]Budget[/TD]
[TD]Actual Spent[/TD]
[TD]Remaining[/TD]
[TD]notes[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Car Payment[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD](This data bar shows nothing[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Food[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]50[/TD]
[TD]This data bar shows a little left[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Brewing[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]This shows a longer bar but isnt full (I'd like it to be full based on percentage but still show the $100)[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Rent[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]This is the longest and fullest bar because it is the most value but I want it to be the same as all other 100 percents but show $400[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I am essentially asking if you can have the length of the bars to be driven by the percentage, but the label on the bars to be driven by the absolute value?
Here is an example of my data set.
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Category[/TD]
[TD]Budget[/TD]
[TD]Actual Spent[/TD]
[TD]Remaining[/TD]
[TD]notes[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Car Payment[/TD]
[TD]150[/TD]
[TD]150[/TD]
[TD]0[/TD]
[TD](This data bar shows nothing[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Food[/TD]
[TD]400[/TD]
[TD]350[/TD]
[TD]50[/TD]
[TD]This data bar shows a little left[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Brewing[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]This shows a longer bar but isnt full (I'd like it to be full based on percentage but still show the $100)[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Rent[/TD]
[TD]400[/TD]
[TD]0[/TD]
[TD]400[/TD]
[TD]This is the longest and fullest bar because it is the most value but I want it to be the same as all other 100 percents but show $400[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]