Error Bar chart showing min, max and average of two series

[Chets]

I have a rate table showing various job roles. There are two types of rates for each job role - Employee rate and Contractor rate. I'd like to present a bar chart that shows Employee and Contractor Average rate with minimum and maximum rate as error bar. The chart will be filtered by job role.

Attached is the sample showing the data and an illustration of the chart I'm trying to create.

 

[Somendra Misra]

Hi,

Can you see this file Sheet1, I had used direct calculations and not pivot to generate the chart.

Regards,

 

[Chets]

Thanks, Somendra. This will work. I will need to adjust the filter to allow user to select all or many values.

 

[Chets]

Hmm! When I use your method in Excel 2011 for Mac it works great; however it does not work on Excel 2013 (Standard) on Windows 8! The formula generated #VALUE! on array references $A$2:$A$19, $C$2:$C$19, and $D$2:$D$19.

 

[Chets]

Hmm! When I use your method in Excel 2011 for Mac it works great; however it does not work on Excel 2013 (Standard) on Windows 8! The formula generated #VALUE! on array references $A$2:$A$19, $C$2:$C$19, and $D$2:$D$19.

I figured it out. The whole formula expression was missing {...} squiggly brackets.