(Probably) simple dashboard question

[excel_stuff]

Hi colleagues,

I am struggling with perhaps a simple dashboard question. I am trying to replicate bar charts in KPI dashboard - revisited II (available on chandoo.org). My minimum value and first quartile are below zero while median, third quartile and max are positive. I also have a target KPI and the score for the company in question (depicted using + sign). However, I am struggling to get it right and would appreciate help. Here are the numbers:

Min = -3
First quartile = -1.5
Median = 0.5
Third quartile = 1.5
Max = 3

Thank you!

 

[Hui]

like this :

 

[excel_stuff]

Thanks, Hui. Sorry for the late reply. This is very helpful. Attached is what I was looking for.

Now I need advice on drawing confidence interval for the point estimate (see attached). I would really appreciate your help.

 

[Hui]

See how this looks


I added Horizontal Error Bars which are linked to your cells
Note that the negative error bar assumes a positive value and subtracts it anyway, so the value in the cell is change from - to + (Highlighted cell)
I also changed it to 5% so you can see it

I reformatted the Marker to be a bit smaller as it hid the Error bars

 

[excel_stuff]

Excellent! Exactly what I wanted. I was making the mistake of using a negative number which didn't work. You are a Ninja!!

 

[excel_stuff]

Sorry just a follow-up. I can easily add vertical error bars from the chart layout menu but cannot find horizontal error bars. Any suggestions? Thanks.

 

[Hui]

What version of Excel do you use?

 

[excel_stuff]

Excel 2010. Thanks.

 

[Hui]

Select the chart
Goto the Chart Tools, Layout, Error Bars Tab
Select more Error Bars Options
Add a vertical error bar
choose Fixed Value, 10
close the Vertical Error Bar Dialog
You should see both sets of error bars


Now click on the vertical error Bar,
Press Delete

Right Click on the Horizontal Error bars and Format Error Bars
Select Custom
In the Specify Value dialog, select the cells to link your error bar values

 

[excel_stuff]

Excellent. This works perfectly! Thank you again!