Incell bar graph to change its color beyond a point

[venkatesh]

Hi I'm Venkatesh.

I'm analyzing a set of costs to see whether the costs have run over the budget or spent less. For example if Electricity cost has a budget of $1000 and the actual spend accounts for $1500, I want the incell bar in two colors; green for budget and red for the overrun. On the other hand if cost is spent less than the budget the bars must indicate the underspend in green. The attached excel sheet will give a better understanding.

Please suggest as to how I can do this in one cell.

Thank you.

 

[Nightlytic]

Hi Venkatesh, I liked the solution on using windings, I never saw anything like that. I attached some usual stacked charts that might achieve what you're looking for.

 

[Chihiro]

Unfortunately, you can't achieve this via formula. Or CF.
There's no functionality to change font colour in function. And to change font colour within, you'd need to do it one character at a time.

You could code it in VBA.

Or alternately use marker to denote last budget box.
Ex: =SUBSTITUTE(REPT("n",C2/100),"n","x",B2/100)


Another alternative is to set different character for budget portion.
Ex: =SUBSTITUTE(REPT("x",C2/100),REPT("x",B2/100),REPT("n",B2/100))

 

[venkatesh]

Hi I'm Venkatesh.

I'm analyzing a set of costs to see whether the costs have run over the budget or spent less. For example if Electricity cost has a budget of $1000 and the actual spend accounts for $1500, I want the incell bar in two colors; green for budget and red for the overrun. On the other hand if cost is spent less than the budget the bars must indicate the underspend in green. The attached excel sheet will give a better understanding.

Please suggest as to how I can do this in one cell.

Thank you.

Hi Venkatesh, I liked the solution on using windings, I never saw anything like that. I attached some usual stacked charts that might achieve what you're looking for.

Thank you very much for the quick response. This will be of good use.

 

[venkatesh]

Unfortunately, you can't achieve this via formula. Or CF.
There's no functionality to change font colour in function. And to change font colour within, you'd need to do it one character at a time.

You could code it in VBA.

Or alternately use marker to denote last budget box.
Ex: =SUBSTITUTE(REPT("n",C2/100),"n","x",B2/100)
View attachment 47733

Another alternative is to set different character for budget portion.
Ex: =SUBSTITUTE(REPT("x",C2/100),REPT("x",B2/100),REPT("n",B2/100))
View attachment 47732

Thank you for the quick response. Assure you that I'll make use of this valuable effort. In the meantime I got a reply from a graphical perspective.
Cheers

 

[p45cal]

You can get this:

by clicking the button in the attached, and selecting either cells C10:C24, or just the single cell C2, when asked.
It uses the chart in cell K3 as a sort of template, which gets duplicated and repositioned.
The in-cell bars will populate the 2 columns to the right of the selected cells. Those two columns will get overwritten by formulae, alhough the result won't be visible. The bars will resize with the cells. The size of the bars will depend on the max actual budget of the selected cells (the longest bar will occupy about 90% of the width of the 2 columns).
Don't get too click-happy; charts are produced wholesale and no charts are deleted by the macro, so be careful to delete unwanted ones.

 

[venkatesh]

You can get this:
View attachment 47745
by clicking the button in the attached, and selecting either cells C10:C24, or just the single cell C2, when asked.
It uses the chart in cell K3 as a sort of template, which gets duplicated and repositioned.
The in-cell bars will populate the 2 columns to the right of the selected cells. Those two columns will get overwritten by formulae, alhough the result won't be visible. The bars will resize with the cells. The size of the bars will depend on the max actual budget of the selected cells (the longest bar will occupy about 90% of the width of the 2 columns).
Don't get too click-happy; charts are produced wholesale and no charts are deleted by the macro, so be careful to delete unwanted ones.

Thank you for your solution PAL
.

 

[p45cal]

In the meantime I got a reply from a graphical perspective.

On the web?
Could you link to that here (or share that solution)?

 

[venkatesh]

No P45cal; another Chandoo forum member shared this solution through stacked charts. Please find attached.

Tks.