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)


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:


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


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


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