BF174 HW09: Data Analysis and Similar Functions/Formulas
RAND, RANDBETWEEN; Histogram, Random Numbers, Samples, Descriptive Statistics with Functions
1)From Erin’s web site, open “File to change” for HW09 – save as username09.xlsx
2)If you have not done this already in Excel: add Analysis Toolpak and Analysis Toolpak – VBA (File/Options/Add-Ins/Go…)
3)Select the 3 sheets – then, in the header, type your name(s) and surname(s)
– then, make only one sheet tab active
In Histogram, use a histogram to show cholesterol levels for the data in Column B, with the following criteria:
4)First, put the BIN in column C: from <=150 to >225, in increments of 25
5)Start output for the histogram data in E2; show cumulative percentage and a chart
6)In the BIN data output table: show ranges (not just single numbers); optimal width 3 columns; make E2:G2 and column E bold; show one decimal for G3:G7
7)In chart area: remove the legend and make the chart about 15 rows tall
8)In plot area: remove cumulative percentage; show no gap; add 2pt black border lines
9)Make the X and Y axes bold; make Y axis show in increments of 2
10)Click out of chart, then SAVE
In Numbers, do the following; as you work, use ctrl-End and ctrl-Home to check that you, in fact, have numbers in row 1501 (for questions 11-14):
11)Starting in A2, use data analysis to generate 1500 random numbers, which are normally distributed, with a mean=0 and a standard deviation=1
12)Starting in B2, use data analysis to generate 1500 random numbers, which are uniformly distributed, between 0 and 1
13)Starting in C2, use a function to generate 1500 random numbers, between 0 and 500
14)Starting in D2, use a function to generate 1500 random integers, between -500 and 500 – copy/paste over so that the integers in column D do not change
15)Starting in F2, use data analysis to find 75 periodic samples, from Column A; make the first 2 numbers in column F bold; find these same numbers in column A, and also make them bold
16)Starting in G2, use data analysis to find 75 random samples, from Column B; make the 1st number in column G bold; then, find this number in column B, and make it bold
17)SAVE
In Data:
18)Use Descriptive Statistics for the data in B:D; put output in F1, showing Summary Statistics, Confidence Level for Mean of 95%, 2nd Largest K, and 2nd Smallest K
19)Do the following to neaten the output data that is in F1:K18: shift the top row to the right by one cell; delete 2 unnecessary columns so that you have output data in F1:I18; make 1st row wrap, bold, and center; make column F bold; optimal width cols F:I
20)In J1, type: Using Functions or Formulas
21)In J3:J17: use functions and/or formulas to find the same answers that are in I3:I18 – as you confirm that answers are the same, if you wish, you may make the answers in cols I and J bold; for Quiz08, if you can do all of these correctly – bonus 0.5 pt
22)SAVE, then close Excel