+ Reply to Thread
Results 1 to 5 of 5

Asymmetrical error bars on series bar chart for proportion confidence intervals

  1. #1
    Registered User
    Join Date
    03-31-2021
    Location
    New York
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    3

    Asymmetrical error bars on series bar chart for proportion confidence intervals

    Hello, I have a bar chart that I am trying to add confidence intervals to. The confidence intervals are based on proportion and are asymmetrical. I can add the confidence intervals as error bars, and I can add them if they are asymmetric, but since my data is grouped, it creates a series, and excel will not let me add a confidence interval to only one of the bars in the series. It applies the CI to every bar even in a different group, even though they have different CI's. Is it possible for excel to even do this? Do I need to arrange my data differently? Can I still keep the same chart format while removing the series aspect?

    I included the excel file so it is easier to understand what I am getting at. The columns 1, 2, and 3 contain the data and are grouped by the columns and rows. A, B, and C, are all the same color bar. I would really prefer if they could stay grouped by the column and row so that they stay the same color and keep the legend, but I could probably edit the color of each bar to match if I need to. The confidence intervals are in the 1L 1H etc columns. L representing the lower value and H representing the higher value. I color coded them so you could see which CI corresponds to which data point. Currently the error bars are wrong so disregard those. Basically, I need each bar in the graph to have a different error bar despite the bars being grouped together. Any help is appreciated! Thanks!
    Attached Files Attached Files
    Last edited by lcol6574; 03-31-2021 at 11:16 PM. Reason: add screenshot

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,831

    Re: Asymmetrical error bars on series bar chart for proportion confidence intervals

    Have you tried using Custom values for the error bars rather than Standard Deviation? The Custom Values is on the format dialog page for the error bars

    You may need to apply a calculation to the existing values, subtracting the column height, to get the error bars to reflected intended positions
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    03-31-2021
    Location
    New York
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    3

    Re: Asymmetrical error bars on series bar chart for proportion confidence intervals

    Hi Andy, I have tried Custom Values. I think that is the only way for me to put in the exact numbers I want. The issue is whenever I try to apply the custom value to one of the bars, it applies it to all of the bars in that series (each blue bar, orange, and gray) and I can't put a custom value on each single bar (each bar in the chart has a different CI therefore needs a different custom value). I think this is because they are grouped together, however I also need them to stay grouped together to best represent the data (if possible).

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,289

    Re: Asymmetrical error bars on series bar chart for proportion confidence intervals

    I'm not certain I understand what you are trying to, but here's what I did:

    1) I need to calculate the length of each error bar, and these calculations need to be in cells that easily correspond to the chart data point they apply to. In this case, your data series are each in a 3 row x 1 column range (B3:B5 for series 1, for example), so my error bar calculations also need to be in a 3 row x 1 column range. I enter =ABS(E3-B3) into L3, =ABS(F3-B3) into M3, =ABS(G3-C3) into N3, =ABS(H3-C3) into O3, =ABS(I3-D3) into P3, and =ABS(J3-D3) into Q3. Copy/paste/fill this row down into rows 4 and 5.
    2) In the chart, select the series 1 error bars -> format error bars -> custom value -> and enter/choose M3:M5 for the positive error bar values and L3:L5 for the negative error bar values. Repeat with the corresponding columns for series 2 and series 3.

    If I have understood what you are trying to do, that should be close to what you want. If I've misunderstood, help us understand what you want to do and we should be able to help get the desired effect.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    03-31-2021
    Location
    New York
    MS-Off Ver
    Microsoft Office Professional Plus 2019
    Posts
    3

    Re: Asymmetrical error bars on series bar chart for proportion confidence intervals

    That worked!! Thank you so much, I can't believe I didn't try that sooner. You have saved so much headache and frustration for me. I guess it was one of those things where it is obvious but I couldn't see it and kept going around it. Thank you again!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with Confidence Intervals
    By excelhelp10101 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2021, 11:43 PM
  2. Chart confidence intervals around regression (or trend) line
    By LeAnne in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 06-28-2013, 11:06 AM
  3. [SOLVED] adding confidence intervals to the bar chart
    By MalgosiaMen in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 05-10-2013, 06:10 AM
  4. confidence intervals and lower/upper error bars
    By kerrymaid in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-24-2012, 01:14 PM
  5. Confidence Intervals
    By skizz135 in forum Excel General
    Replies: 0
    Last Post: 11-15-2009, 08:30 PM
  6. [SOLVED] need to make chart w/ confidence intervals and don't know how
    By penguinchick in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-20-2005, 02:05 PM
  7. How to I incorporate SD from a data series into chart error bars?
    By carlosPharm.D in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-02-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1