Box and whisker plots #2

Can anyone help?

Does excel have a box plot graph function. i.e. like a bar chart except the 
bars are "floating" with the top value on the box representing the 75th 
centile, the bottom of the box representing the 25th centile, then error bars 
from the top of the box representing 90th centile and error bars from bottom 
of box representing 10th centile.  I also would like to put a line within the 
box representing the median.

Thanks

John
0
JRD (9)
8/17/2007 1:24:02 PM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
671 Views

Similar Articles

[PageSpeed] 43

Hi,

See Jon's page
http://peltiertech.com/Excel/Charts/BoxWhisker.html

Cheers
Andy

-- 

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"JRD" <JRD@discussions.microsoft.com> wrote in message 
news:6C22F9BC-124A-4894-A039-A6C45D72599E@microsoft.com...
> Can anyone help?
>
> Does excel have a box plot graph function. i.e. like a bar chart except 
> the
> bars are "floating" with the top value on the box representing the 75th
> centile, the bottom of the box representing the 25th centile, then error 
> bars
> from the top of the box representing 90th centile and error bars from 
> bottom
> of box representing 10th centile.  I also would like to put a line within 
> the
> box representing the median.
>
> Thanks
>
> John 

0
andy9699 (3616)
8/17/2007 1:28:49 PM
Hi Andy,

Does this work if some of my percentiles have negative values

I have followed the instructions but the box plots do not seem to correspond 
to the percentile numbers

John

"Andy Pope" wrote:

> Hi,
> 
> See Jon's page
> http://peltiertech.com/Excel/Charts/BoxWhisker.html
> 
> Cheers
> Andy
> 
> -- 
> 
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
> "JRD" <JRD@discussions.microsoft.com> wrote in message 
> news:6C22F9BC-124A-4894-A039-A6C45D72599E@microsoft.com...
> > Can anyone help?
> >
> > Does excel have a box plot graph function. i.e. like a bar chart except 
> > the
> > bars are "floating" with the top value on the box representing the 75th
> > centile, the bottom of the box representing the 25th centile, then error 
> > bars
> > from the top of the box representing 90th centile and error bars from 
> > bottom
> > of box representing 10th centile.  I also would like to put a line within 
> > the
> > box representing the median.
> >
> > Thanks
> >
> > John 
> 
0
JRD (9)
8/18/2007 9:56:16 AM
Hi,

There appears to be a screen shot of a box plot with negative values so 
I assume yes.

Cheers
Andy

JRD wrote:
> Hi Andy,
> 
> Does this work if some of my percentiles have negative values
> 
> I have followed the instructions but the box plots do not seem to correspond 
> to the percentile numbers
> 
> John
> 
> "Andy Pope" wrote:
> 
> 
>>Hi,
>>
>>See Jon's page
>>http://peltiertech.com/Excel/Charts/BoxWhisker.html
>>
>>Cheers
>>Andy
>>
>>-- 
>>
>>Andy Pope, Microsoft MVP - Excel
>>http://www.andypope.info
>>"JRD" <JRD@discussions.microsoft.com> wrote in message 
>>news:6C22F9BC-124A-4894-A039-A6C45D72599E@microsoft.com...
>>
>>>Can anyone help?
>>>
>>>Does excel have a box plot graph function. i.e. like a bar chart except 
>>>the
>>>bars are "floating" with the top value on the box representing the 75th
>>>centile, the bottom of the box representing the 25th centile, then error 
>>>bars
>>>from the top of the box representing 90th centile and error bars from 
>>>bottom
>>>of box representing 10th centile.  I also would like to put a line within 
>>>the
>>>box representing the median.
>>>
>>>Thanks
>>>
>>>John 
>>
0
andy9699 (3616)
8/18/2007 11:48:10 AM
The more advanced techniques successfully account for negative values for 
the percentiles.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Andy Pope" <andy@andypope.info> wrote in message 
news:%23Hk%23p2Y4HHA.4676@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> There appears to be a screen shot of a box plot with negative values so I 
> assume yes.
>
> Cheers
> Andy
>
> JRD wrote:
>> Hi Andy,
>>
>> Does this work if some of my percentiles have negative values
>>
>> I have followed the instructions but the box plots do not seem to 
>> correspond to the percentile numbers
>>
>> John
>>
>> "Andy Pope" wrote:
>>
>>
>>>Hi,
>>>
>>>See Jon's page
>>>http://peltiertech.com/Excel/Charts/BoxWhisker.html
>>>
>>>Cheers
>>>Andy
>>>
>>>-- 
>>>
>>>Andy Pope, Microsoft MVP - Excel
>>>http://www.andypope.info
>>>"JRD" <JRD@discussions.microsoft.com> wrote in message 
>>>news:6C22F9BC-124A-4894-A039-A6C45D72599E@microsoft.com...
>>>
>>>>Can anyone help?
>>>>
>>>>Does excel have a box plot graph function. i.e. like a bar chart except 
>>>>the
>>>>bars are "floating" with the top value on the box representing the 75th
>>>>centile, the bottom of the box representing the 25th centile, then error 
>>>>bars
>>>>from the top of the box representing 90th centile and error bars from 
>>>>bottom
>>>>of box representing 10th centile.  I also would like to put a line 
>>>>within the
>>>>box representing the median.
>>>>
>>>>Thanks
>>>>
>>>>John
>>> 


0
jonxlmvpNO (4558)
8/20/2007 12:44:37 PM
You have to combine the more complicated data structure of this tutorial:

http://peltiertech.com/Excel/Charts/StackedColumnsAboveAndBelow.html

with the box and whisker example here:

http://peltiertech.com/Excel/Charts/BoxWhiskerV.html

The Box and Whisker Utility 
(http://peltiertech.com/Excel/Charts/BoxWhisker.html) generates a box and 
whisker chart from any data set, including sets with positive and negative 
values.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message 
news:OKBpgfy4HHA.3940@TK2MSFTNGP05.phx.gbl...
> The more advanced techniques successfully account for negative values for 
> the percentiles.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Andy Pope" <andy@andypope.info> wrote in message 
> news:%23Hk%23p2Y4HHA.4676@TK2MSFTNGP05.phx.gbl...
>> Hi,
>>
>> There appears to be a screen shot of a box plot with negative values so I 
>> assume yes.
>>
>> Cheers
>> Andy
>>
>> JRD wrote:
>>> Hi Andy,
>>>
>>> Does this work if some of my percentiles have negative values
>>>
>>> I have followed the instructions but the box plots do not seem to 
>>> correspond to the percentile numbers
>>>
>>> John
>>>
>>> "Andy Pope" wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>See Jon's page
>>>>http://peltiertech.com/Excel/Charts/BoxWhisker.html
>>>>
>>>>Cheers
>>>>Andy
>>>>
>>>>-- 
>>>>
>>>>Andy Pope, Microsoft MVP - Excel
>>>>http://www.andypope.info
>>>>"JRD" <JRD@discussions.microsoft.com> wrote in message 
>>>>news:6C22F9BC-124A-4894-A039-A6C45D72599E@microsoft.com...
>>>>
>>>>>Can anyone help?
>>>>>
>>>>>Does excel have a box plot graph function. i.e. like a bar chart except 
>>>>>the
>>>>>bars are "floating" with the top value on the box representing the 75th
>>>>>centile, the bottom of the box representing the 25th centile, then 
>>>>>error bars
>>>>>from the top of the box representing 90th centile and error bars from 
>>>>>bottom
>>>>>of box representing 10th centile.  I also would like to put a line 
>>>>>within the
>>>>>box representing the median.
>>>>>
>>>>>Thanks
>>>>>
>>>>>John
>>>>
>
> 


0
jonxlmvpNO (4558)
8/20/2007 1:50:58 PM
Reply:

Similar Artilces:

HQ Global Customer #2
Hi We have come across a problem where the last visit on the purchase history tab in the global customers option at headquarters and also in store operations manager local customers is not being updated. Our customer is using version 1.2.180. I have also tested this on my system using version 1.2.184. Please can you investigate this. I will look forward to your response. Thanks Guppy Bassi PS - I have not received a Microsoft response. Please can you look into this and reply to this e-mail. Hi Guppy, You will not receive a Microsoft response from this board. You need to create ...

Lists Box
I have a form with fields; ID, DATE and QUANTITY. The Date andQuantity Field I've changed into a List Box showing the two columnsrespectively and the ID field is left as a normal text box. When Iclick on any item in the list box the ID change change according towhich ever entry I have selected. Unfortunetly ID field staysconstant!!Any suggestionsThanks Hi, Asif,did you include the ID field in the ListBox?The easiest solution:1) Add the ID field to the RowSource.2) Point the BoundColumn to the ID field.3) Set the ID field column width to 0.HTHVlado"Asif" <asifhashmani@hotmail.c...

In-Box
My in-box label in left rail shows 4 messages, however they do not download, while at the same time other new messages download. Has Outlook forgotten how to count? You probably have a filter applied to your view so that some messages are not shown. Verify that your view is set to the Messages view via the View-> Current View menu If that doesn't work either, use Customize Current View from the same menu and use the Reset Current View button. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, H...

Combo Box Result
Hi all, I have two unbound combo boxes (c1 and c2) that based on the value of c1, will determine what shows for c2. By selecting c1, it will correctly limit to what c2 shows, but when I select the option for c2, it is not choosing the correct value. The result set for c2 is based off a table, that has the following format: ISID ISD ITID 1 red 1 2 blue 1 3 green 1 4 red 2 5 blue 2 6 green 2 c1 will select the ITID, which then limits what I see in c2 to the correct set (I even brought in the ISID column to make sure the numbers ...

Message box with Combo box function
Is it possible to create a combo box within a message box i.e. whenthe user clicks on preview report a message box appears and says"which report do you want to open?" and a combo box displays thedifferent reports available with the user clicks, upon clicking therelevant report opensThanks * Asif wrote:> Is it possible to create a combo box within a message box i.e. when> the user clicks on preview report a message box appears and says> "which report do you want to open?" and a combo box displays the> different reports available with the user clicks, upon clickin...

combo box query
Hi, I have a combo box that includes a drop down box when selected. Can this be in any way altered so you can scroll down the box ? at the moment, i have to select the box with my mouse, then browse through the contents using either my up / down buttons or the little down arrows. It would be so much easier to scroll up & down. When i select the combobox, presently it scrolls excel and not the box. And help appreciated. Nige ...

Edit Box Display Control
I have a dialog-based app, and I want to have a read-only edit box to display changing output(when the user hits a button, the displayed numbers change in the edit box). Can I change the background color of the edit box, or should i just draw a rectangle and write text to it to control the background? Thanks, Matt Just change the readonly style of the editbox. Take a look at the CEdit::SetReadOnly() method. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "matt" <bonmatt10@cs.com> wrote in message news:c2838b8f.0312281100.2574e9ef@posting.goo...

To chage the string of the Message Box
Hello, I was told the following query, and it works good. How can I change the Notes By to be a Msg String : "Dicatat Oleh". I want it as a variable, because i have already the table of String message, so that it can be in many languages. INSERT INTO Catatan ( NO_URTANGT, TGLCATATAN, WKTCATAT, Subject, Notes ) SELECT a.NO_URTANGT, a.TGLCATATAN, a.WKTCATAT, a.Subject, a.Notes & [Note by] FROM [C:\Churchdata\Churchdataconso\BkEnd\Hahomion_be.mdb].Catatan AS a; Thanks in advance for many helps. -- Frank Situmorang Are you talking about a stored query that you...

Macro Question #2
Hi guys, I have an excel spreadsheet with a load of columns with various bits of data in them. Basically i am struggling to get the VB code together which will find specific entries in a specific column and then remove the relevant row in which that specific piece/pieces of data are held, UNLESS, a specific entry is named in another cell. For example, i want to find Monday, Tuesday and Wednesday in column A and then remove the specific rows they pertain to, UNLESS column F has 'NOT' written in it....... Hope that makes sense. Many Thanks, Public Sub ProcessData() Dim i ...

Automatic update the number in a text box
Hello: I have text boxes in my charts. In that text box, I need to copy a number from another sheet each time I update charts. I am wondering if there is a way that I can update the number automatically. Thanks in advance! Is the number in the same cell every time? Here's how to create a new text box that's linked to a cell (whenever I try linking an existing text box, Excel just draws a new one on top of the old one). Select the chart. Press the equals key. Navigate to the other sheet and select the cell with the mouse. The formula bar reads like: =Sheet2!$B$2 Press enter. - ...

how do I get the x-axis to plot values rather than categories in .
how do I get the x-axis to plot values rather than categories in Excel By using an XY rather than a LINE chart -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Boggins" <Boggins@discussions.microsoft.com> wrote in message news:2D963E4D-93C3-4E8A-83F1-A51943ED4CAA@microsoft.com... > how do I get the x-axis to plot values rather than categories in Excel ...

can't change default file location #2
When I click "open file", Excel always open "C:\Program Files\Common Files\Microsoft Shared\PROOF". I have changed the "Tools-Options-General-Default file location" to "My Documents" but still no effect. Please help. Did you exit Excel before trying again? >-----Original Message----- >When I click "open file", Excel always open "C:\Program >Files\Common Files\Microsoft Shared\PROOF". I have >changed the "Tools-Options-General-Default file location" >to "My Documents" but still no effect. ...

Turn on Edit option in Combo Box
How do you turn on the Edit option in a Combo Box in Excel 2000? I have no problem entering the drop-down part, but cannot get it to allow editing. Please help. Pam Williams Pam What kind of combobox? Forms toolbar, Control Toolbox, or Data Validation. What do you mean by editing? Do you want to edit an entry on the list, add a new entry to the list, or just be able to type in a value that's not on the list? -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "Pam at Greer" <Pam at Greer@discussions.microsoft.com> wrote in message ne...

Filtering Combo box items based on related table field- Please Help!
Hi im stuck on filtering a combo box. I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables: Categories Table: CategoryID (PK) Description Users Table: UserID (PK) Username CategoryID (FK on Categories.CategoryID) Jobs Table: JobID (PK) CategoryID (FK on Categories.CategoryID) JobDetails Table: JobID (FK on Jobs.JobID) UserID (FK on Users.UserID) Then in the job details when listing users for a job (many users can be for one job) i would like to only show the us...

Comment Box or description box
Is there a way to assign the comment box to appear when a certain item is sold, so therefore a description can be inputed. you can use the Item Message (item definition - Special tab) to remind the cashier to set the comment on those items when sold. "Gary" wrote: > Is there a way to assign the comment box to appear when a certain item is > sold, so therefore a description can be inputed. ...

Outlook Configuration #2
Good morning, I want to know if their is a way to configure outlook using a script. What i mean is that i need to change the configuration of 100 outlook to point to another exchange server. Thank you for the help Ramy > I want to know if their is a way to configure outlook using a script. What > i > mean is that i need to change the configuration of 100 outlook to point to > another exchange server. > > Thank you for the help > > Ramy Outlook Profiler is loved by all who use it. ...

Help with Excel #2
Im new to Excel and need o do a work place shift roster. I need to put in the shifts people work eg: E (early) L (lates) N for nights ... but i also need these to have values eg an E is 7.5 hours a N is 12 hours ... i need this so at the end of each week i can sum the total hours each member of staff does... can anybody tell me how please Hi Try =SUMIF(A1:A10,"E")*7.5 =SUMIF(A1:A10,"N")*12 change ranges to suit -- Regards Roger Govier "Jezzer" <Jezzer@discussions.microsoft.com> wrote in message news:A4E5DB47-DD0B-4EFE-98CA-916925ABAC6A@microsoft...

How do I add input data in the input ranges in drop down boxes.
I have a spreadsheet that was sent to me that has drop down boxes with data that you can select and it is link to other drop down boxes. I would like to add additional data in the drop down box range but cannot find where you add additional data to select in the drop down boxes. Thanks, Charlie If the dropdown lists are created by Data Validation, you can select the cell, and choose Data>Validation. In the Source box, you'll see the range where the list is stored. If that's not it, right-click on the dropdown list, and choose Format Control On the Control tab, you'l...

Excel.exe error message #2
I am getting this message when trying to open a shared Excel Spreadsheet. "Excel.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log has been created. How do I resolve this problem? File could be corrupt in which case you need to get someone with either XL2002 or 2003 to open it using Open and Repair, or if you are using 2000 or 97 then it may be your toolbar customisation file is getting close to blowing up on you. If so then go look for a file called *.xlb and either rename it to *.old or just delete it. NOTE:- This is your toolb...

Printing Labels #2
Is there a way to print labels off of a excel spreadsheet? hi, yes there is. excel will print to any printer regardless of the media the data is printed on. You may have to play with it to get it to fit your labels. you may even have to trun the text 90 degrees(vertical). we have a special file that contain certain special purpose label that our receiving department needs to print periodily and they print on the same bar code printer as all the bar coded labels print on. regards FSt1 "Todd Nelson" wrote: > Is there a way to print labels off of a excel spreadsheet? You m...

Business Portal 2.5, user cannot get requisitions assigned to him?
UserA created three requisitions and sumit them to his supervisor, UserB. In database table ReqMgmtDocument, I can find these three records and OriginatingName is UserA and CurrentAssigned is UserB. In Business Portal, UserB got in Employee -> Purchase Requests -> Filter by: Assigned to me, and got "Query returned no result". Does anyone know how to solve this problem? Thank you very much. stien ...

The From box
Every time I create a new email the "from" box appears. How do I turn this off. Create a new message; View-> uncheck From Field -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Mike" <anonymous@discussions.microsoft.com> wrote in message news:26fb01c4703e$1ee1e320$a301280a@phx.gbl... > Every time I create a new email the "from" box appears. > How do I turn this off. ...

Debug Error #2
Hi All, Could anyone throw any light on the following:- I have a WIN XP home machine running Outlook 2000 for mail. I have set up 2 profiles one for me and one for my wife. When I first turn on the machine and go into oulook (either profile) everything works fine. Then I close Outlook and go back in on the 2nd profile and get the error box :- "Microsoft Visual C++ Debug Library Debug Error! Program C:\...........\OUTLOOK.EXE abnormal program termination (Press Retry to debug the appliacation) Abort Retry Ignore" No matter what you choose the appliacation closes and then...

Formatting: display blank when "O", 2 decimal when a number is d
I want to display a blank whenever there is a "O" and I need to display 2 decimal when there is a number. so far I found 0;-O;;@ this formula helps me display a blank cell when needed, however it doesn't give me the 2 decimal I need. Thanks E One way 0.00;-0.00; assuming you mean zero and not the letter O, you can't format for letters -- Regards, Peo Sjoblom "ED" <ED@discussions.microsoft.com> wrote in message news:5DCD806F-5B2E-494D-947A-8BB7D160F90B@microsoft.com... >I want to display a blank whenever there is a "O" and I ne...

font sizes for To, From, and Subject text boxes
How do you change the font sizes for these 3 textboxes when composing an email? When you set the fonts under Options->Mail Format, only the font in textarea for the message body gets changed. Thanks, Ernest Correct. That's what that setting is for -- to change the message font. If you want to change the font sizes of other elements, you'd have to design a custom form. In some cases, changing the overall Windows font size might also have an effect. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Dev...