• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Boxplots?

J

johnmw1

New Member
Hi,


I have just completed 5 boxplots in Excel 2007 for an assignment I have been given with my subject of statistics.


The boxplots have been made by following various instructions online, but I don't think they truly represent the information as they don't show outliers for instance.


Would anyone be willing to have a look at my assignment please and point me in the right direction. I assume attachments can't be added here, so would have to come to some other arrangement.


Sorry for having to ask this question but I have spent far more time than one should have to on what should be a relatively easy chart to produce.


Thanks

John
 
John

Can you post a copy on one of the free file sharing sites and put a link here ?

Make sure there is no confidential or personal related info within the file
 
or have a look at these


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

http://www.bloggpro.com/box-plot-for-excel-2007/
 
Hi Hui,


Thanks for your help.


Those links you posted are actually the ones I was following to create what I have, but I don't think they are complete in the fact that they are not showing the outliers. They just show Min, Max.


http://www.mediafire.com/?sharekey=22cafd6bee52ce05e5c3dee5769931ece5dfa837cda1d609a543906a5faff527


I think the link above is OK I'm still trying to figure out mediafire? I thought I had put all my files in one folder but it appears to have separated them.


When you download the file you will see the main EX3 spreadsheet which has 4 sheets to it. Sheet 1 is my main page with Sheets 2 & 3 with a few workings on it and on Sheet 4 there is another version of how to create boxplots with outliers etc, but to be honest it's got beyond my capability. It certainly shows all the formulas but when I tried it on Sheet 1 I kept getting errors, so I don't know what I'm doing?


In the other files they are jpegs of another programme I found at some university that worked it all out for me and shows outliers etc but it not excel!


Thanks

John
 
John


a few comments


Sheet4 - Your Box Plot is using filtered data which has the outliers removed (Column D) you should use column B in the Min and Max equations in G11 and G12


Sheet1 - Your Std Deviation and Range Calculations in Rows 103 and 105 are values not formulas and hence may be in error


Also a lot of the Box Charts are linked to other spreadsheets ?


All the data you need for the Box Plots is in the area A91:F96

If you follow through the instructions at

http://www.bloggpro.com/box-plot-for-excel-2007/

I get good Box Plots that do exactly what they should and include all the outliers
 
Hi Hui,


Ah yes that Sheet 4 now that you have reminded me did create a formula to remove highs and lows, so now I'm guessing as to whether this is the correct way or not?


My Sheet 1 Row 103 &105 Std Deviation & Range were created by Excel using Data Analysis, so if there is something incorrect I'm not sure of how to fix it.


When you say the Box Charts are linked to other spreadsheets, I'm sorry again but I'm not sure how to fix the problem. I think I may have created them in a totally different spreadsheet and then copied and pasted from memory.


I will go back to your link above and have another go. :)


Two of the major problems I'm having is we are being taught Statistics by a lecturer who has never taught it before and also I think is learning as he is writing on the whiteboard. The other problem is he is even more clueless about Excel so therefore cannot show us any examples of how to create charts and graphs. He also of course is handing out Excel assignments for which I have no idea and am self learning along the way, but by heck its taking a lot of time of which I don't have a lot of, as I have 9 other subjects a week.


Thanks


John in Adelaide
 
John


All the data you need for the Box Plots is in the area A91:F96

If you follow through the instructions at

http://www.bloggpro.com/box-plot-for-excel-2007/

It works as required


The Formulas in Rows 103 and 105 should be


B103 =STDEV(B4:B89)

B105 =MAX(B4:B89)-MIN(B4:B89)


and copy both accross

Not that any values in that area are used elsewhere anyway.
 
You must log in or register to reply here.
Back
Top