excel assigment

Question #1

· Mean: =average(select all the data from each column)

· Median, Mode, Max, Min: =name(select all data from each column)

· Range: =Max-Min

· Standard Deviation: =stdev(select all data from each column)

· To get the data for the second column: select first set of data on left, ctrl c, ctrl v on other side 

· Finishing 

· Select all the data 

· Center

· Create a table: grid box icon, ‘all borders’

· Decimal place: Numbers, number, decimal place 2

· For “Entire class” do all the same but select both columns for the data set

· Percentiles: use population reference data

· Shortest: =norm.dist(Min, population reference mean, population reference SD, TRUE)*100

· Tallest: =norm.dist(Max, population reference mean, population reference SD, TRUE)*100

· Graph Information

· Boys, Girls on top; Average, SD on left side

· Copy the averages and SD’s from the original data columns 

· Title it “Reference for Graph”

· Graph

· Select boys, girls, and both averages

· Insert, column, first 2d column chart w/ 4 columns

· Layout, error bars, more error bar options, under ‘deviation’ only select ‘Plus’, custom, specify value, click the button beside positive error value, select the 2 SD’s, click the button next to it, OK.

· Layout, axis titles, primary vertical axis title, rotated title, Name it “Height (in inches)” depending on the information given. 

· Layout, chart titles, centered overlay, “Heights of Boys and Girls”

Question #2

· Frequency

· Use =COUNTIF for one criteria (Ex: <60)

· =COUNTIF(select all data, “<60”)

· Use =COUNTIFS for two+ criteria (Ex: 90-100)

· =COUNTIFS(select all data, “>=90”, select all data again, “<=100”)

· Histogram

· Select all new data (ranges and frequencies), insert, column, first 2D column, Title it “Histogram”, Layout, axis titles, primary vertical axis title, rotated title, Name it “Frequency”

Question #3

· Pie Chart

· Select empty box right under where you typed “Pie Chart”, Data tab, Data validation, data validation, settings, allow: list, click button next to source, select all regions (only the whole first row Region1, Region2, etc.), OK

· Use drop-down arrow on first empty box and select “Region 1”, skip a space below, NEXT to that empty space (to the right of it) put =Region 1, copy and paste the entire first column of titles (Blouses, Skirts, etc.)

· Should look like this: 

Region 1

  Region 1

Blouses

Etc.

· Beside Blouse and Under Region 1, =if(Region1=blank space before all the regions,first data result,if(region1=region2,first data result under region2, if(region1=region3, first data result under region3, if(region1=region4,first data result under region4, first data result under region5

· Put a $ between all of the letters & numbers except the data results (Ex: if($B$13=$C$2,C3), Copy that answer, paste it on all the next ones. 

· You can change the data depending on the region by changing the region with the pull-down arrow. 

· Select all the NEW data, insert, pie drop-down, first 2D pie chart

· Line Chart

· Select ALL data, insert, line, first line chart

· Name X and Y axes 

· Clustered Bar Chart

· Select data, insert, bar chart, first 2D bar chart

· Name X and Y axes

· Stacked Bar Chart

· Select all data, insert, bar chart, second bar chart on 2D (they look stacked)

· Name X and Y axis

· Pivot Table, Avg, SD

· Select data, insert, pivot table on far left of the toolbar, select existing worksheet, click button by Location, click an empty space below the data, OK, in Pivot Table Field List on the right toolbar click boxes of all regions, Under values go to value field settings and change everything to average. 

· Select data, insert new pivot chart, Existing worksheet, Location: one space below other chart, OK, select all regions again on the right-side toolbar, Under values go to value field settings and change everything to SD

· Flip the table: select original data table, ctrl C, select an empty box below pivot tables, paste icon in the top left toolbar, under that is transpose, Insert 2 more pivot tables for the flipped data, 

Question #4

· X and Y will be given 

· “If x is …, what is y?”

· Select all KNOWN data, Insert, Scatter Plot, First Scatter Plot with dots, Remove the “Series” from the graph, Name X and Y

· Layout tab, Trendline, More Trendline Options, Linear, Click “Display Equation on Chart”, under the data, type that equation, type it again right beside it,  but put in the unknown for X, highlight the answer 

Question #5

· T-Test

· Rule 1: Just 2 columns of data = candidate for T-test

· Rule 2: Anytime there is Before and After or Pre, Post for one group = Paired T-test

· Rule 3: If it is 2 different groups = Independent Sample T-test with Unequal Variance

· Then, decide if it is one-tail or two-tail

· If you take a side and make an assumption (leaning toward one side) = One-Tail

· If you do not take sides and are simply asking a question = Two-Tail 

· Data with more than two columns = can do t-test but just have to do multiple T-Tests, with each paired up. 

· Before and After

· Select all data, Data tab, Data analysis (far right), scroll down to different T-test options, Before and After is a PAIRED TWO SAMPLE t-Test, OK, Input variable one range as the first column of data, second variable range as the second column, check Labels, Alpha: 0.05, Output Range (choose the location of where you want the table)

· Look at the paragraph and see if the question asked is taking sides, if it is two-tail: Go under the two-tail P-value in the table, if the value is less than 0.05 (SIGNIFICANT) put the P text in green, if the value is more than 0.05 put it in red (NOT SIGNIFICANT)

· Write a hypothesis conclusion ? 

Question #6

· Example: Two columns: <4hr, >6hr

· Two different groups = Two samples with unequal variance 

· Look at question asked and see if it is One-Tail or Two-Tail. There is a hypothesis in this example so a side is being taken = One-Tail

· Data tab, Data Analysis on far right, scroll down to t-Test options, choose Two Sample Assuming Unequal Variances, OK, Input variable one range as the first column of data, second variable range as the second column, check Labels, Alpha: 0.05, Output Range (choose the location of where you want the table)

· Look at the One-Tail P value on the table, (1.83E-12 = less than 0.05

· Write a hypothesis conclusion

Question #7

· Example: color, black and white, combination

· Data tab, data analysis, ANOVA: single factor, OK, select all data, check labels, Output Range (choose the location of where you want the table, OK

· Look at P-value and see if it is greater than or less than 0.05, write conclusion

Question #8

· Example: US & India on X-axis, Geography & History on Y-axis

· Data, Data analysis, Anova: Two factor with replication, OK, input range: select all the data, Rows per sample: # of kids from the problem, Output Range (choose the location of where you want the table, OK

· Look at the P-value, LOOK AT ALL OF THE VALUES ON UNDER P and label them as green or red (significant and not significant), (sample=countries, columns=subjects, write conclusion hypothesis