Need a thermometer chart (sort of)

[deekarvois]

I've spent the better part of two days... today I tried to adapt a bullet chart for my purpose but I am having problems getting what I want to show. I am trying to develop a chart that shows total vacant space on floors in one color (or the degrees in a thermometer chart); vacant space per floor behind (stacked column chart?), an indicator of actual total building space (part of the stacked column chart); and another indicator for total usable space (slightly smaller number than total building space). Is it possible to do this? (hope you can adapt my table to see the data)


Floor Vacant (sq. ft.) Total Floor Space Building Space Total w/Fls -1 -2 Building Space Total w/o -1 -2

9th 10,542 22,652 505,272 487,149

12th 11,809 22,652 505,272 487,149

15th 2,394 22,654 505,272 487,149

All 24,745

 

[Luke M]

The image I'm getting is a column chart, 1 column for each floor (and 1 for total?), with each column having different shaded levels. Is this correct?


If so, a stacked column chart is what you want. You need to prep the data a little to get the correct values. In some helper cells, we need to get the numbers prepared in order of magnitude, smallest first. This, formula for Floor vacant is simply:

=B2

Total Floor space: (Total floor - unused)

=C2-B2

Total building usable space: (Total building used - total floor)

=F2-C2

Total build space: (Total building - total building used)

=D2-F2


For ease of reading, you might want to duplicate your labels over to near the helper cells. Then, select these cells and build the stacked column chart.


EDIT:

Since total building numbers are so much larger, you may want to move that to a seperate graph. Or, go with a ratios approach, and compare how each floor is being used compared to it's total capability.

 

[deekarvois]

The purpose of the chart is to show how low the vacancy rate is, so it is important to reflect the comparison on one chart. Is the stacked column chart the best chart to demonstrate this? Thanks.

 

[Luke M]

Since the story is more about the rate, I think a 100% stacked chart would work for you here. The 4 columns would be 9th, 12, 15th, and total.


Now, you do need to get all your data "similar". The first two columns contain vacant and total, while the latter two (of which, we only need 2 cells) compare used with total. Some simple subtraction could get you all the numbers you need. In the end, you'll have 4 columns plotted against an y-axis of percentages showing what percentage of each floor/column is vacant. Will this work?