Presenting Penetration

[starnold]

Does anyone know a way of presenting data to show a under/over presentation?

I will have a list of data where 100 is the centre point and I want a way to show the under/over penetration from that point.

For example I could have the below data:

Category Spend
A 150
B 50
C 500
D 10

For categories A & C it would be presented to the right of the centre point as they are over 100 and B & D they would be presented to the left of the centre point as they are under 100?

 

[gehusi]

In A1, enter =if(B1<100,100-B1,"")
In C1, enter =if(C1>100,C1-100,"")

Copy those down as far as your data goes.

 

[starnold]

thanks for the reply but I wanted a graphical representation of the data not jsut the actual figures

 

[gehusi]

Assuming your data ranges from B1 to B8,

To the left of your data, enter

=IF($B1<100,REPT("█",(100-B1)/(MAX($B$1:$B$8)-100)*25),"")

To the right of your data, enter

=IF($B1>100,REPT("█",(B1-100)/(MAX($B$1:$B$8)-100)*25),"")

Right align the left hand data and left align the right hand data. Format each to the desired font color.

Adjust the range to fit your data, and changing the 25 in the formula will make all the bars proportional larger or smaller to suit your needs.