I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Cell based Charts

Dietmar P. came up with a nice cell based chart at Controller Akademie ©.

I applied some changes and enhancements like introducing auto-scaling and adding two parameters:

- A number format to allow for a general format change if necessary

- A Boolean parameter to control whether to switch the green and red colouring of the chart (if you hire a contractor then you might like to show staying below the budget with a green colour, but if you like to present sales figures then it might be the other way round)

Budget_vs_Actual_Chart

Formula in H9:
=REPT(TEXT(F9-D9,Format)&" ",F9-D9<0)&REPT(Symbol,(F9-D9<0)*ABS(ROUND(F9-D9,0))*Scaling_Factor)
In I9:
=REPT(Symbol,(F9-D9>0)*ABS(ROUND(F9-D9,0))*Scaling_Factor)&REPT(" "&TEXT(F9-D9,Format),F9-D9>0)

In sheet Param all parameters have been collected:

Cell_based_charts_Param_Screen

These parameters have been named as shown in the Name Manager window:

Cell_based_charts_Name_Manager

A possible switch between colours green and red is implemented via a conditional format, for example in column I:

Cell_based_charts_Conditional_Format

If you are interested in a 20 KB sample file which was used to create the outputs above, go to my Download page, please.

Sulprobil   Get it done   Contact   Disclaimer   Download