Jan 23 2021 11:09 AM
Jan 23 2021 11:09 AM
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-
Jan 23 2021 11:44 AM
@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.
Jan 23 2021 03:18 PM - edited Jan 23 2021 11:56 PM
@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.