Clustered Coloumn Chart with independent cells

[Erefin]

Dear sages of Excel,

I'm having a data set of 3 clusters, namely Inattention, Hyperactivity and Impulsivity, which in turn consist of 3 groups (ADHD+, ADHD, Control) and one of each group holds 2 groups in turn, namely X and Y.
I would like to create a clustered coloumn chart, with a slight space between each group and slightly larger spaces between each cluster. Afterwards, I want to add error indices FOR EACH COLOUMN INDIVIDUALLY - and this is where I run into problems.
Because when I use format 1...

1:
IIIII ADHD2 ADHD Control
Ina: X 1.5 II 2.5 II 3.5
Ina: Y 1 IIII 2 IIII 3

IIIII ADHD2 ADHD Control
Act: X 1.5 II 2.5 II 3.5
Act: Y 1 IIII 2 IIII 3

IIIII ADHD2 ADHD Control
Imp: X 1.5 II 2.5 II 3.5
Imp: Y 1 IIII 2 IIII 3


...excel automatically "links" the different groups together, i.e. when I want to change the error indices for ONLY ADHD2 and Ina:X I can't do so because Excel only allows me to cahnge the error indices for all coloumns of ADHD2. The same thing happens for error indices for ADHD and Control.
As such, my question is the following: How do I tell Excel that all cells (i.e. ADHD2 x Ina:X, ADHD2 x Ina: Y, ADHD x Ina:X, ADHD x Ina: Y, ...) need to be independent of one another, i.e. that the program to set error indices for each coloumn individually?
You'd do me a great favor by helping me out, I really have no clue how to achieve that.

Cheers
Erefin

[MrShorty]

I am having trouble visualizing or understanding what you have tried and what you want.

As a guess, I assume you are talking about adding custom error bars to your column chart. Your sample data does not include your error values/calculations, so I don't know how you are putting the error bars into Excel. Assuming my guess is correct, I would expect:

1) Enter/calculate the desired error amounts in an adjacent column (2 columns, if I want different values for the positive and negative error bars).
2) In the chart, add the error bars to the desired data series.
3) Format the error bars to use "custom value" or "value from cells" and tell Excel to use the columns I created in step (1) for the positive and negative error values.

Help file: https://support.office.com/en-us/art...5-864049a145f0

Is my guess close?

[Erefin]

Dear MrShorty,

thank you very much for your swift reply. Your guess is indeed correct, I want to add standard errors to the coloumns, but I haven't listed the standard errors here - though they do exist in my excel sheet.
I've tried to add more coloumns (see table 2) but it results in a clustered coloumn chart with VERY big distances between groups (ADHD, ADHD+, CG). As such, I could only use this approach if there is any way to drastically reduce the distance between groups - and to also decrease the distance between clusters a bit more. Do you, or any other, know of such a way?

Cheers
Erefin
Attachment 623879 Attachment 623880

[MrShorty]

Something went awry adding the attachments, so I cannot see exactly what you have done. Some of the tools for adding attachments to this forum don't work right. To reliably add an attachment, click on "Reply to Thread" or "Go advanced" to bring up the site's main post editor. Below the main text editing window is a link to "Manage attachments" that will bring up the site's main file uploader (usually in another tab/window). You can then use the upload utility to upload your file and it should attach correctly to your post.

Your description of "large gaps" between groups and such suggests to me that the problem is how you are arranging the data in the spreadsheet. I find a lot of charting questions really end up being about how to arrange data in the spreadsheet. For "data in rows" I would expect the spreadsheet to look something like:

Please Login or Register  to view this content.

Transpose that matrix for data in columns.

Does that help?

[Erefin]

Thank you for the advice with the images - are they correctly displayed right now? Table / chart 1 dispalys a similiar approach to the one right now, with the problem being that no individual error values can be defined for each group*cluster variation. The second table/chart has that individuation, but due to the format of the spreadsheet (which I've chosen to achieve the disjunct group*clusters), gaps between coloumns and clusters are way to big.
I usually define error values by going to Layout > Error Indices > User defined to set values. However, I'm not familiar with entering error indices by a table of the spreadsheet as you've done. Would you mind explaining how to do so - and how to tell Excel "Cell 1 gets error values from cell A, cell " from cell B etc."?

Cheers
Erefin

[MrShorty]

1st: what I see Excel doing:
Picture 1 Shows a clustered column chart with 8 categories (Inattention No WN, Inattention WN, blank, Act: No WN, Act: WN, blank, IMP: No WN, IMP, WN) and 3 data series (ADHD+, ADHD, CG). You have added error bars, but I cannot see what it is using for the error values. It looks like it is using a value close to 0.23 for positive and negative error bars on each data point.
Picture 2 shows a clustered column chart with 9 categories (ADHD+(1), ADHD(1), CG(1), ADHD+(2), ADHD(2), CG(2), ADHD+(3), ADHD(3), CG(3)) and 20 data series. I don't see any error values in the spreadsheet, nor any error bars in the chart. The large gaps in this picture are due to having 20 data series, since Excel reserves the horizontal space for each data series even when the cell for that point is blank. Reducing the gap between categories means reducing the number of data series. Since I am not sure why you arranged the data like this, I cannot be sure what to suggest here.
Your description of adding error bars ( Layout > Error Indices > User defined ) does not exactly match the procedure described in the help file. After following that (or similar) sequence of commands, I would have expected you to click on the "specify value" button next to the "custom" radio button which would bring up a dialog that allows you to select the spreadsheet range where the error values are entered/calculated. I cannot explain why Excel did something different. This tutorial shows in more detail how to get to the custom error bar settings: https://www.exceltip.com/tips/how-to...xcel-2010.html See if that is more helpful.

As a guess: If I wanted to add custom error bars to the chart in picture 1, I would:
1) Have three columns to the right of picture where I entered/calculated the error values.
2) Select a data series -> add error bars -> format error bars to use the custom value -> specify value and point to the appropriate column where the errors for that series are entered/calculated.

If I wanted a similar chart with the ADHD+, ADHD, CG as the categories and the present categories as data series, I would start with the chart in picture 1 and:
1) Enter/calculate the desired error values in the rows beneath the table in picture 1.
2) Select the chart and Switch Row/Column so that the data is in rows. This would give me a chart with 3 categories and 8 data series.
3) Select a data series in the chart -> add error bars -> format error bars -> custom value -> specify value -> select the appropriate row with the error values for that data series.

[Erefin]

Dear MrShorty,

your solution for cahrt 1 worked out great - thank you so, so much :-) Now the chart is finally working the way it should!

Cheers
Erefin