• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need chart with secondary series (single value)

dianacris

Member
I have a chart I'm trying to build based on tornado data. I have a data table with tornado activity which includes columns for a unique index number (to identify each tornado), date of the tornado (separated into month, day, year cols), and county affected by tornado. The data table includes information from 1950 to 2013. Multiple counties can be affected by a tornado so some data repeats because the only difference on the row is the county name.

I built a pivot table that counts each tornado by year and the pivot table can be filtered (using a slicer) by County. I created a chart showing the tornadoes by year for the selected county(s). What I need now is to add a line across the column chart at a point which represents a result from a couple of average and std deviation formulas.

Example: XYZ county has tornadic activity from 1950-2013 in the range of 0-8. If my formula result is 3.15, then I want a line to go across the chart at the axis value of 3.15. Here's a graphic of what I need. http://1drv.ms/1n2MzIJ

If a pivot table is not the way to go, then please let me know I've headed down the wrong path here. Because my calculations are based on the average number of tornadoes for the selected county(s), this approach was the easiest for me. I just need to figure out how to chart this final value. If an overlapped, transparent chart will work, how do I get the value to span across rather showing as a single data point?

Thanks in advance!!
Diana
 
I was able to change my pivot chart to a regular chart and by using some dynamic named ranges it now updates when the slicer/pivot table updates. I then figured out how to get my single value populated in a separate column (repeated down as far as I need to go for the number of years) and added a transparent chart with the single value charted. Here's a quick pic of the result so far. http://1drv.ms/1pMvDew Lots more to do! Thanks!
 
Back
Top