Question

can someone show me how to enter this into EXCEL

I need to know how this is supposed to be entered and the EXCEL formula to use...
what should my spread sheet look like?
tutorials aren't showing me... please help!

thank you

12. Weights of Diet Colke. Construct a fre- quency table for the weights (in pounds) given below of 36 cans of Diet Coke. Start the first bin at 0.7750 pound and use a bin width of 0.0050 pound. Discuss your findings 0.7773 0.78740.78680.7802 0.78060.7907 0.7879 0.7833 0.79230.7910 0.7813 0.7859 0.7822 0.7896 0.7839 0.7861 0.7874 07852 0.7870 0.7826 0.7837 0.7872 0.7923 0.7760 0.77580.7822 0.7844 0.7892 0.7879 0.78850781

Answer 1

Open the excel (fresh sheet) and enter the given data. Also enter bin range. Enter 1^st value of bin range as 0.7750(since given). For 2^nd value of bin range, add bin width of 0.0050 to 0.7750 which equals 0.7800 and again add 0.0050 to enter 3^rd value. Continue this till you feel enough. So, the value of bin range is 0.7950 because the data set has no value greater than 0.7950. All this is shown in the following Excel image:

Now select "Data" on the top of the screen - then click on "Data Analysis" which is on top right - a screen with Data Analysis with Analysis Tools appears where you must select "Histogram" and click "OK" which is shown in the following image:

After clicking "OK" on the above screen, Histogram screen appears where you have to enter Input Range which is our data entered as $A$6:$D$14 for which you can directly select the data or enter it manually, click F4 after typing A6 and then again after typing D14 to get $ symbols. Similarly select Bin Range: $B$17:$B$21 and select Output Range where you wish your output to display on the screen: $D$16.Leave others as they are. Click "OK" which is shown here:

Data ValidationGroup Data Analysis ?>Solver Clear " Dat Reapply Ungroup, a- Subtotal Consolidate Advanced 1. Text to Remove-What-If Analysis- Columns Duplicates mmmmm Histogram Input Input Range: $A$6:$D$14 Cancel Bin Range: $B$17:$B$21 Help Labels Output options o Output Range: D16 New Worksheet Ply: New Workbook Pareto (sorted histogram) Cumulative Percentage Chart Output

After clicking "OK" on the above screen, the output will be displayed on the given Output Range which is shown below:

I changed the output color to Red in order to distinguish from we entered. Our output is "Bin and Frequency".

Now, by looking at our output, we need to create grouped frequency distribution. For this, we ignore first and last rows of the output (without labels) where frequency is 0 and 0 and consider other rows. For bin range of 0.78 the class of weights is 0.7750 - 0.7799. (since, 0.7800 - 0.0001 =0.7799) and it's frequency is 4. It is similarly taken for other bin ranges. All this is shown here under: