Home

95% Confidence Interval to Bar Graph

%3CLINGO-SUB%20id%3D%22lingo-sub-334804%22%20slang%3D%22en-US%22%3E95%25%20Confidence%20Interval%20to%20Bar%20Graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334804%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20add%2095%25%20confidence%20intervals%20to%20my%20bar%20graph%20in%20excel.%20I%20have%205%20categories%2C%20each%20with%20one%20number%20(that%20I%20was%20told%20are%20averages)%20and%20I%20was%20given%20an%20upper%20and%20lower%20confidence%20interval%20for%20each%20number.%20However%2C%20excel%20doesn%E2%80%99t%20recognize%20these%20as%20CIs%20since%20they%20were%20not%20calculated%20in%20excel%20(and%20I%20don%E2%80%99t%20have%20the%20raw%20data).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20to%20add%20custom%20error%20bars%20but%20the%20upper%20and%20lower%20CIs%20were%20always%20off%20by%20about%201.%20I%20don%E2%80%99t%20think%20this%20method%20is%20working%2C%20is%20there%20another%20way%20to%20do%20it%20in%20excel%3F%20Am%20I%20doing%20something%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20appreciated.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-334804%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335173%22%20slang%3D%22en-US%22%3ERe%3A%2095%25%20Confidence%20Interval%20to%20Bar%20Graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335173%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F282045%22%20target%3D%22_blank%22%3E%40Julie1%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBut%20it%20shows%20correctly.%20Let%20take%20Q1%20bar%3A%3C%2FP%3E%0A%3CP%3Evalue%20%3D%2018.28%3C%2FP%3E%0A%3CP%3ELCI%20%3D11.69%2C%20thus%20it%20shows%2018.28-11.69%3D6.59%3C%2FP%3E%0A%3CP%3EUCI%3D27.42%2C%20error%20bar%20shows%2018.28%2B27.42%3D45.70%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-335108%22%20slang%3D%22en-US%22%3ERe%3A%2095%25%20Confidence%20Interval%20to%20Bar%20Graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-335108%22%20slang%3D%22en-US%22%3E%3CP%3EI%20tried%20to%20input%20the%20numbers%20directly%20as%20shown%20in%20the%20image%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20%2C%20but%20the%20error%20bars%20are%20way%20off.%20Am%20I%20doing%20something%20wrong%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20329px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72848iF819DB784C61FBAD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72849i542DBE4AF424F248%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture1.PNG%22%20title%3D%22Capture1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20635px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72850i9193444350773479%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture2.PNG%22%20title%3D%22Capture2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-334817%22%20slang%3D%22en-US%22%3ERe%3A%2095%25%20Confidence%20Interval%20to%20Bar%20Graph%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-334817%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20general%20it%20works%2C%20standard%2095%25%20or%20custom%20(could%20be%20array%20constant%20or%20cells%20range)%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20663px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F72782i0B78632F4994ECBB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Julie1
New Contributor

I am trying to add 95% confidence intervals to my bar graph in excel. I have 5 categories, each with one number (that I was told are averages) and I was given an upper and lower confidence interval for each number. However, excel doesn’t recognize these as CIs since they were not calculated in excel (and I don’t have the raw data).

 

I tried to add custom error bars but the upper and lower CIs were always off by about 1. I don’t think this method is working, is there another way to do it in excel? Am I doing something wrong?

 

Any assistance would be appreciated. Thank you. 

3 Replies

In general it works, standard 95% or custom (could be array constant or cells range) 

image.png

 

I tried to input the numbers directly as shown in the image @Sergei Baklan , but the error bars are way off. Am I doing something wrong?

Capture.PNGCapture1.PNGCapture2.PNG

Hi @Julie1 ,

 

But it shows correctly. Let take Q1 bar:

value = 18.28

LCI =11.69, thus it shows 18.28-11.69=6.59

UCI=27.42, error bar shows 18.28+27.42=45.70

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies