Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
A bit stuck with this, but wondered if the collective wisdom of STW could help.
I have two ranges of data, range A discrete values between 0 & 20, and range B discrete values between 1 & 10. What I want to do is to produce a scatter plot with with range A on the X axis, and Mean(range B) with SD bars on the Y axis (for each value of A there will be multiple values of B). I can plot A vs Mean B easily enough using AVERAGEIF (on a new sheet) but I can't get SD bars to work.
Anyone any pointers?
Thanks,
Andy
I'm not sure I exactly understand - but it sounds like you are looking for a "STDEVIF" function, which off the top of my head doesn't exist.
A simple (but not necessarily very elegant) way to do it would be to create a table with 20 columns, one for each possible value of A. Then use an =IF($A2=C$1, $B2,) to split out the values into a kind of matrix. (assuming the top left of your data is in A2, and you put the values of A (categories) across the cells C1, D1, E1 etc. You can then fill that across the whole table.
At the bottom you can easily add Average() and Stdev() formula and then plot the graph.
One table for your A and B values, a second table for the summary values using the SuMif, Countif and AverageIf functions.
Then in the values table add a third column calculating the deviation of the B value from the average B value using a vlookup on A to the summary table.
In the values table add a fourth column calculating the square of the deviation.
In the summary table add a column summing the new squares of the deviation col using sumif.
In the summary table add column dividing the sum of the squares by either the countif total (for a population StDev) or the countif -1 for a sample stdev.
In the summary table add a column taking the square root of the previous mean squares column to give the std dev.
Cross your fingers that you don't have a circular reference then plot your graph.
STDEVIF is exactly what I'm looking for, if it existed :-/
Will give your suggestions a try & report back - thanks
Andy