Statistics

%3CLINGO-SUB%20id%3D%22lingo-sub-2090291%22%20slang%3D%22en-US%22%3EStatistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090291%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20anyone%20familiar%20with%20how%20to%20use%20Excel%20to%20work%20Statistical%20Problems%3F%26nbsp%3B%20I%20am%20taking%20a%20Survey%20of%20Statistics%20class%20in%20which%20we%20must%20use%20Excel%20Spreadsheet%20to%20figure%20out%20problems.%26nbsp%3B%20I%20am%20not%20Excel%20savvy%20in%20the%20least.%20Any%20help%20would%20be%20greatly%20appreciated!%26nbsp%3B%20Thank%20you-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EStacie%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2090291%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090338%22%20slang%3D%22en-US%22%3ERe%3A%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940174%22%20target%3D%22_blank%22%3E%40StacD1495%3C%2FA%3E%26nbsp%3BExcel%20offers%20over%20a%20hundred%20statistical%20functions.%20If%20you%20describe%20your%20problem%20and%20perhaps%20upload%20an%20example%20of%20the%20data%20you%20are%20working%20with%2C%20somebody%20here%20might%20be%20able%20to%20help%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090351%22%20slang%3D%22en-US%22%3ERe%3A%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20your%20reply.%26nbsp%3B%20I%20have%20a%20data%20file%20that%20is%20set%20up%20in%20Excel%20with%203%20tabs%20(data%20set).%26nbsp%3B%20I%20have%20to%20calculate%20the%20Descriptive%20Statistics%26nbsp%3B%20for%20each%20of%20the%20three%20name%20items.%26nbsp%3B%20Then%20we%20have%20to%20create%20equally%20size%20bins%20using%20the%20Data%20Range.%26nbsp%3B%20I%20am%20particularly%20stuck%20on%20how%20to%20CREATE%20the%20BINS%20because%20I%20will%20have%20to%20create%20a%20histogram%20from%20the%20findings.%26nbsp%3B%20This%20is%20open%20textbook%20work%20but%20I%20am%20still%20struggling.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2090545%22%20slang%3D%22en-US%22%3ERe%3A%20Statistics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2090545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F940174%22%20target%3D%22_blank%22%3E%40StacD1495%3C%2FA%3E%26nbsp%3B%26nbsp%3B%20First%2C%20let%20me%20say%20that%20creating%20a%20histogram%20involves%20some%20%22art%22%20as%20well%20as%20science.%26nbsp%3B%20There%20are%20choices%20to%20make%3B%20and%20those%20choices%20can%20affect%20the%20shape%20of%20the%20histogram.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20at%20the%20%22gross%20profit%22%20worksheet%2C%20for%20example....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20challenge%20is%3A%26nbsp%3B%20how%20many%20bins%20to%20have%3F%26nbsp%3B%20This%20choice%20has%20a%20major%20impact%20on%20the%20shape.%26nbsp%3B%20But%20unfortunately%2C%20there%20is%20no%20%22best%22%20answer.%26nbsp%3B%20So%20this%20will%20require%20some%20experimentation.%26nbsp%3B%20One%20recommendation%20is%20SQRT(n)%20for%20n%20data%20points%20(up%20to%20a%20reasonable%20limit%20like%2031).%26nbsp%3B%20You%20have%2025%20data%20points%3B%20so%20we%20might%20start%20with%205%20bins.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20next%20question%20is%3A%26nbsp%3B%20what%20are%20the%20bin%20limits%3F%26nbsp%3B%20Your%20data%20ranges%20from%204.1%20to%2092.2.%26nbsp%3B%20We%20might%20choose%20those%20as%20the%20limits%20of%20the%20histogram.%26nbsp%3B%20But%20we%20might%20choose%204%20and%2093%20because%20the%20equal%20width%20of%20the%20bins%20is%20(93-4)%2F5%20%3D%2017.8%2C%20a%20nice%20%22round%22%20number%2C%20so%20to%20speak.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20case%2C%20we%20might%20calculate%20the%20bin%20width%20in%20I22%20with%20the%20formula%20%3D(93-4)%2F5.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20we%20might%20enter%2093%20into%20K27%2C%20and%20the%20formula%20%3DK27-%24I%2422%20into%20K26.%26nbsp%3B%20Then%20copy%20K26%20into%20K23%3AK25.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20the%20limit%20of%20the%20leftmost%20bin%20is%2021.8%2C%20not%204.%26nbsp%3B%20The%20reason%20is%3A%26nbsp%3B%20in%20Excel%2C%20bin%20limits%20are%20on%20the%20right.%26nbsp%3B%20So%20we%20can%20think%20of%204%20as%20the%20limit%20of%20a%20non-existent%206th%20bin%20on%20the%20left.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20we%20can%20calculate%20the%20frequencies%20in%20L23%3AL27.%26nbsp%3B%20You%20might%20use%20the%20Histogram%20Data%20Analysis%20tool.%26nbsp%3B%20I%20prefer%20to%20enter%20the%20formulas%20directly.%26nbsp%3B%20Select%20L23%3AL27%2C%20type%20the%20formula%20%3DFREQUENCY(F6%3AF30%2CK23%3AK27)%2C%20and%20%3CU%3E%3CEM%3Earray-enter%3C%2FEM%3E%3C%2FU%3E%20the%20formula%20by%20pressing%20%3CU%3E%3CEM%3Ectrl%2Bshift%2BEnter%3C%2FEM%3E%3C%2FU%3E%20instead%20of%20just%20Enter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20might%20experiment%20with%204%20and%206%20bins%20to%20see%20if%20their%20histograms%20give%20you%20a%20different%20and%20perhaps%20more%20desirable%20distribution%20of%20the%20data.%26nbsp%3B%20Remember%20to%20calculate%20a%20different%20bin%20width%20each%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E-----%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20that%20gives%20you%20some%20insight.%26nbsp%3B%20But%20remember%3A%26nbsp%3B%20you%20should%20follow%20the%20instructions%2C%20if%20any%2C%20of%20your%20teacher%20or%20text%20for%20how%20to%20decide%20the%20number%20of%20bins%20and%20the%20bin%20limits.%26nbsp%3B%20Again%2C%20there%20is%20no%20%22best%22%20or%20%22right%22%20way%20to%20do%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

Is anyone familiar with how to use Excel to work Statistical Problems?  I am taking a Survey of Statistics class in which we must use Excel Spreadsheet to figure out problems.  I am not Excel savvy in the least. Any help would be greatly appreciated!  Thank you-

 

Stacie

5 Replies

@StacD1495 Excel offers over a hundred statistical functions. If you describe your problem and perhaps upload an example of the data you are working with, somebody here might be able to help you.

@Riny_van_Eekelen  Thank you for your reply.  I have a data file that is set up in Excel with 3 tabs (data set).  I have to calculate the Descriptive Statistics  for each of the three name items.  Then we have to create equally size bins using the Data Range.  I am particularly stuck on how to CREATE the BINS because I will have to create a histogram from the findings.  This is open textbook work but I am still struggling.

@StacD1495 Sorry! Statistics is not my speciality. But since you uploaded your file, I'm confident that someone out here who does know statistics will get back to you.

@StacD1495   First, let me say that creating a histogram involves some "art" as well as science.  There are choices to make; and those choices can affect the shape of the histogram.

 

Looking at the "gross profit" worksheet, for example....

 

The first challenge is:  how many bins to have?  This choice has a major impact on the shape.  But unfortunately, there is no "best" answer.  So this will require some experimentation.  One recommendation is SQRT(n) for n data points (up to a reasonable limit like 31).  You have 25 data points; so we might start with 5 bins.

 

The next question is:  what are the bin limits?  Your data ranges from 4.1 to 92.2.  We might choose those as the limits of the histogram.  But we might choose 4 and 93 because the equal width of the bins is (93-4)/5 = 17.8, a nice "round" number, so to speak.

 

In any case, we might calculate the bin width in I22 with the formula =(93-4)/5. 

 

Then we might enter 93 into K27, and the formula =K27-$I$22 into K26.  Then copy K26 into K23:K25.

 

Note that the limit of the leftmost bin is 21.8, not 4.  The reason is:  in Excel, bin limits are on the right.  So we can think of 4 as the limit of a non-existent 6th bin on the left.

 

Now we can calculate the frequencies in L23:L27.  You might use the Histogram Data Analysis tool.  I prefer to enter the formulas directly.  Select L23:L27, type the formula =FREQUENCY(F6:F30,K23:K27), and array-enter the formula by pressing ctrl+shift+Enter instead of just Enter.

 

You might experiment with 4 and 6 bins to see if their histograms give you a different and perhaps more desirable distribution of the data.  Remember to calculate a different bin width each time.

 

-----

 

I hope that gives you some insight.  But remember:  you should follow the instructions, if any, of your teacher or text for how to decide the number of bins and the bin limits.  Again, there is no "best" or "right" way to do it.

@Joe User   Wow, thank you so much.  We have to select 5 bins and the range is minimum and maximum as calculated per Data Analysis tool.  This helps give me a better guide.