Excel Question, make chart where each bar is the average of each column
March 10, 2016 9:02 AM Subscribe
This seems simple to me but can not find the answer on-line. If I have several columns of data, each column is just one variable with several entries. How can I make a chart from this data where each chart column is the average value of each data column. Then add Std. Dev bars to this?
How many columns? Could you just put some adjacent columns with averages and standard deviations (or put those on top of the data columns)?
e.g. if data columns are columns B through G
H1: "B"
H2: =average(b:b)
H3: =stdev(b:b)
Fill right to column N, and plot the averages that you've just calculated. When Excel asks for error bars, point it to the standard deviation cells.
There may well be an automatic way to do it, but in the time it takes to find that you could just do this.
posted by Huffy Puffy at 10:01 AM on March 10, 2016
e.g. if data columns are columns B through G
H1: "B"
H2: =average(b:b)
H3: =stdev(b:b)
Fill right to column N, and plot the averages that you've just calculated. When Excel asks for error bars, point it to the standard deviation cells.
There may well be an automatic way to do it, but in the time it takes to find that you could just do this.
posted by Huffy Puffy at 10:01 AM on March 10, 2016
You should format the data with at least two columns (date and data) with add columns for relevant descriptive data (type, category, region, whatever) then create a pivot table.
You'll probably want to group dates into month, year, quarter, whatever.
Next you'll get into the part that matters: summarize the data by average and std dev.
Adding a chart at this point is just a few clicks from the pivot table menu.
Once you get this set up (and tbh good data architecture should come out in a flat format from your source system) it's rather low maintenance to add to each day/month/year or if you have a really good data set up, connect the spreadsheet and pivot table to your data warehouse using power pivot and the connections manager.
posted by toomanycurls at 5:12 PM on March 10, 2016
You'll probably want to group dates into month, year, quarter, whatever.
Next you'll get into the part that matters: summarize the data by average and std dev.
Adding a chart at this point is just a few clicks from the pivot table menu.
Once you get this set up (and tbh good data architecture should come out in a flat format from your source system) it's rather low maintenance to add to each day/month/year or if you have a really good data set up, connect the spreadsheet and pivot table to your data warehouse using power pivot and the connections manager.
posted by toomanycurls at 5:12 PM on March 10, 2016
This thread is closed to new comments.
Pivot tables are fairly easy and quick to learn, an online tutorial will help you get started. It's learning how to pick what you want from drop-down menus.
posted by lizbunny at 9:28 AM on March 10, 2016