Excel Quiz

User Generated

FZFN3

Business Finance

Description


Quiz Instructions

use the project file e08h1Assessment.xlsx to answer this quiz questions.

learn the material from: Adisak_lecture_excel_Additional_topic2(Ch8)_2018.pdf


1 Question 3 pts

From the "Educator Assessment" project file "e08h1Assessment", first worksheet. What would be the result of Average Salary of teachers from Veigo Township who have the salary greater than $45,000? (answer in two decimal places)


2 Question 3 pts

From the "Educator Assessment" project file "e08h1Assessment" on the first worksheet. What would be the 3rd Quartile EXC of Salary of all teachers? (answer with one or two decimal places)


3 Question 3 pts

From the "Educator Assessment" project file "e08h1Assessment", first worksheet "Educator Assessment". What would be the Rank EQ of Salary of teacher name Matsumoto from Jackson Township(who have the lower salary)? (answer in number)

Unformatted Attachment Preview

High School Educator Information Last Name Kato Han Yoon Lopez Yamamoto Lee Garcia Thomas Gao Cruz Cruz Ortiz Rodriguez Ma Young Jhadav Jayaraman Sanchez Li He Mehta Hamade Bhatnagar Takahashi Young Park Matsumoto Abdoo Diaz Rodriguez Ito Banerjee Matsumoto Kadam Patil Sasaki Robinson He Lin Thomas Kimura Jackson Smith Williams Hire Date 12/22/2007 10/20/2015 4/23/2014 7/30/1999 12/29/2002 4/16/2014 4/23/2000 10/2/2002 7/16/2006 8/6/2011 9/11/2006 8/16/2005 8/4/2008 1/21/2010 5/4/2010 8/3/2006 10/14/2003 5/30/2009 5/30/1999 1/17/2002 1/13/2014 8/17/2003 11/30/2011 12/17/2010 4/17/2001 5/25/2011 5/11/2005 9/9/2006 12/18/2011 2/12/2015 10/10/2009 5/25/1999 8/30/1995 7/14/2013 8/22/2007 6/21/2014 1/23/2010 9/26/2005 8/25/2015 4/29/2001 4/16/2002 11/17/2008 2/21/2014 2/28/2007 Salary $ 61.065,00 $ 59.913,00 $ 64.052,00 $ 45.305,00 $ 52.691,00 $ 38.827,00 $ 57.307,00 $ 48.384,00 $ 37.575,00 $ 60.913,00 $ 58.134,00 $ 57.394,00 $ 43.815,00 $ 36.288,00 $ 50.920,00 $ 55.953,00 $ 64.261,00 $ 34.301,00 $ 55.257,00 $ 44.450,00 $ 35.259,00 $ 58.478,00 $ 35.015,00 $ 53.994,00 $ 40.878,00 $ 31.386,00 $ 41.571,00 $ 52.633,00 $ 37.769,00 $ 57.269,00 $ 36.029,00 $ 46.440,00 $ 64.508,00 $ 40.637,00 $ 57.902,00 $ 53.385,00 $ 53.359,00 $ 34.434,00 $ 33.971,00 $ 31.168,00 $ 43.802,00 $ 55.820,00 $ 55.430,00 $ 52.630,00 Township Salary Rank Veigo Jackson Jackson Veigo Veigo Veigo Veigo Acorn Jackson Veigo Acorn Jackson Veigo Jackson Veigo Veigo Acorn Acorn Veigo Acorn Jackson Jackson Jackson Veigo Veigo Jackson Jackson Acorn Veigo Veigo Veigo Acorn Jackson Jackson Veigo Acorn Acorn Veigo Acorn Acorn Jackson Jackson Acorn Acorn Lumus Jones Perry Officer Spalding Lockridge 1/30/2006 7/30/2013 8/20/1998 12/10/2001 1/1/2013 8/12/2002 $ $ $ $ $ $ 55.547,00 60.994,00 43.780,00 54.884,00 48.699,00 39.329,00 Jackson Jackson Jackson Acorn Veigo Veigo Summary Information Count Total payroll Average Salary Hired before 1/1/2005 Acorn Township High School Teachers Count Total Payroll Average Salary Hired before 1/1/2005 Quartile Salary (QUARTILE.EXC) 1 2 3 6-8 Test Scores Student ID Test Score 1075 725 1912 325 4196 648 4483 750 6237 585 6284 325 6285 707 6312 684 6353 407 6747 501 6778 282 7025 596 7284 789 7486 621 7717 600 7788 406 8362 234 8843 650 9573 532 9849 550 1625 417 2309 524 2513 737 2834 275 4470 800 5713 353 6695 523 7224 121 7670 641 7718 544 7825 715 8049 799 9410 739 2137 702 2877 700 3817 400 4562 321 4938 187 5701 357 6035 322 6076 665 6141 301 7601 651 Township School # Days Absent Acorn 24 2 Acorn 24 10 Acorn 24 6 Acorn 24 0 Acorn 24 2 Acorn 24 9 Acorn 24 1 Acorn 24 2 Acorn 24 10 Acorn 26 6 Acorn 26 5 Acorn 26 2 Acorn 26 9 Acorn 26 0 Acorn 26 1 Acorn 26 3 Acorn 26 6 Acorn 26 0 Acorn 26 3 Acorn 26 0 Jackson 55 3 Jackson 55 3 Jackson 55 3 Jackson 55 10 Jackson 55 2 Jackson 55 6 Jackson 56 3 Jackson 56 7 Jackson 56 8 Jackson 56 5 Jackson 56 0 Jackson 56 0 Jackson 56 8 Veigo 83 4 Veigo 83 5 Veigo 83 0 Veigo 83 1 Veigo 83 3 Veigo 83 9 Veigo 83 5 Veigo 84 5 Veigo 84 9 Veigo 84 7 Max Test Score 800 Sample Size 50 Standard Deviation Variance Days Absent Frequency 0 5 10 7653 7708 8244 8608 9209 9596 8765 411 253 640 479 462 351 555 Veigo Veigo Veigo Veigo Veigo Veigo Veigo 84 84 84 84 84 84 84 10 7 8 5 3 2 2 Average 517 Correlation Sample High School SAT Scores SAT Score High School 1 SAT Score High School 2 SAT Score High School 3 1436 2065 1944 2014 1229 2163 2308 2134 2010 1763 1431 1965 1544 1710 2243 1347 1727 1815 1383 1310 2005 1504 2150 1250 1677 2163 1835 1313 1225 2172 1245 2309 2037 2223 2155 1673 1794 1581 2273 1639 2343 1375 1882 1519 1444 1417 1938 2222 1698 1839 1669 2042 1513 1601 1691 1878 1702 2324 1778 2120 1224 1204 1311 1674 2255 2098 1963 1821 1259 1834 1941 2174 2344 1585 2220 2322 2183 1977 2241 1774 1747 1253 1209 1213 1470 2352 2244 1931 2325 1981 1608 2160 1560 2283 1959 1345 1670 2247 1386 2183 1678 1343 1383 1359 2396 1849 2139 1299 1220 1569 2304 1430 1965 2188 2084 2030 2313 2398 1761 2112 2025 2395 1885 1400 1757 2366 2197 2235 2252 1531 1490 2200 1572 1627 1580 1606 2149 1364 2352 2256 1361 2332 2250 1232 1485 1228 1410 2008 2272 1233 Test scores Analysis Student ID SAT Score 2500 4437 3028 4276 4366 1345 1673 1795 1704 3740 2475 4634 4084 3306 4167 2540 4945 4193 4986 2543 2238 1809 1317 4971 2758 2030 3721 4999 3105 1461 3635 1615 2047 4829 4855 3009 1234 3633 3377 4529 4602 1286 1114 Absences 1436 2014 2308 1763 1544 1347 1383 1504 1677 1313 1245 2223 1794 1639 1882 1417 1698 2042 1691 2324 1224 1674 1963 1834 2344 2322 2241 1253 1470 1931 1608 2283 1670 2183 1383 1849 1220 1430 2084 2398 2025 1400 2197 Bins 6 0 0 3 7 3 7 1 1 8 7 1 7 4 9 7 0 1 5 8 3 1 4 2 2 1 3 9 7 3 8 6 0 5 9 4 6 8 3 1 4 3 2 0 5 10 1017 4718 2053 3546 4891 3668 2302 3511 3842 1840 1760 1030 2556 2812 4226 1246 3095 4156 4909 4435 4467 1850 4401 2128 2983 2080 1251 1003 3477 2784 2028 3651 2369 4672 1312 1169 3165 1016 4048 2483 3801 2635 3044 3919 4209 4309 4562 1531 1490 2200 1572 1627 1580 1606 2065 1229 2134 1431 1710 1727 1310 2150 2163 1225 2309 2155 1581 2343 1519 1938 1839 1513 1878 1778 1204 2255 1821 1941 1585 2183 1774 1209 2352 2325 2160 1959 2247 1678 1359 2139 1569 1965 2030 1761 3 2 0 0 10 5 3 4 2 0 1 8 10 1 7 7 6 7 8 8 9 10 7 10 3 10 9 10 10 7 6 8 8 10 3 5 9 2 2 5 5 3 8 8 3 6 8 3745 4329 1129 4334 2224 3130 3546 4543 2130 3552 2038 4788 3286 3047 3781 3600 3491 3689 1318 4657 1526 1439 3502 4031 2421 1353 2706 1530 3556 3366 1564 1461 4463 3638 4087 3965 3576 2762 1397 2232 1997 2334 2991 3463 3655 3888 4754 2395 1757 2235 2149 1364 2352 2256 1361 2332 2250 1944 2163 2010 1965 2243 1815 2005 1250 1835 2172 2037 1673 2273 1375 1444 2222 1669 1601 1702 2120 1311 2098 1259 2174 2220 1977 1747 1213 2244 1981 1560 1345 1386 1343 2396 1299 2304 6 1 9 0 4 10 10 0 7 10 8 10 8 10 6 4 7 7 10 2 10 7 4 4 10 8 9 9 1 10 9 9 10 2 6 3 6 10 0 2 8 4 9 6 8 5 6 2283 4803 1236 2579 2987 2713 3764 2321 3590 2469 1985 2389 2663 2188 2313 2112 1885 2366 2252 1232 1485 1228 1410 2008 2272 1233 9 4 10 8 0 4 2 7 0 5 9 1 7 COM S 113 (Introduction to Spreadsheets and Databases) Additional Topics II (Chapter 8) Statistical Functions 1 What will we learn? Why? “Conditional” math and statistical functions ◦ You often only want to sum, average, etc. values that meet certain conditions Calculate relative standing with statistical functions ◦ Compare individual value to rest of dataset (e.g. Your ranking in your class based on exam scores) Measure central tendency ◦ Sometimes you want to describe your data with statistics like average and variance • Create a histogram ◦ Visual display of distribution of data 2 Case Study for Chapter 8 You are the superintendent of K-12 schools in Erie, PA. You are to evaluate teachers’ performance ranking and salary quartiles. You also have to assess students’ standardized testing performance. 3 Conditional Math and Statistical Functions 4 Conditional Math & Statistical Functions We have already seen math and statistical functions such as SUM and AVERAGE Excel has other versions of these functions that perform the same function IF a certain condition is met ◦ Example: Sum all salaries IF the township equals “Jackson” Here are some of the conditional functions: ◦ SUMIF ◦ AVERAGEIF ◦ COUNTIF When more than one condition must be met, use: ◦ SUMIFS ◦ AVERAGEIFS ◦ COUNTIFS 5 Conditional Math & Statistical Functions The conditional functions require these arguments: ◦ the criteria to meet ◦ e.g. “=Jackson” “>40,000” ◦ the range of cells to compare to the criteria ◦ some functions such as SUMIF also need an argument to specify the range of values to sum Examples: ◦ SUMIF(range, criteria, sum_range) ◦ SUMIFS(sum_range, range1,criteria1, range2,criteria2,…) 6 Use ConditionalMath and StatisticalFunctions How it’s different from the Database Functions? (Dsum, Daverage, etc.) To use an Advanced filter, you must first create a “criteria range” ◦ A “criteria range” is a separate area in the sheet that lists the criteria to filter by ◦ You can easily change the criteria and then re-apply the filter Criteria Range Filtered Data 8 Example of DSUM Database Function Database functions =DSUM(database,field,criteria) “Database functions” perform common calculations using an “advanced filter” to select the data to use Some of the most common database functions: DSUM DAVERAGE DMIN DMAX DCOUNT 9 Calculate Relative Standing With Statistical Functions You can determine the “relative standing” of values compared (relative to) other values Examples: ◦ Rank the values from 1 to … ◦ Get the percentile of a value (the 90th percentile) 10 2 Types of Relative Standing Functions 1) Get the Relative Standing of a value ◦ RANK functions calculate ranking for individual values within a list ◦ ◦ Example: the salary $61,065 ranks 4th among salaries PERCENTRANK functions calculate rank as a percentile of the range of values ◦ Example: the salary 68,750 is in the 90th percentile rank 11 RANK.EQ vs. RANK.AVG RANK.EQ ranks values and gives equal values the same rank ◦ Example: A list has the values 50,30,70,50 70 ranks 1st, the 50s rank 2nd, 30 ranks 4th RANK.AVG assigns an average rank to equal values ◦ Example: In the list above, the 50s would rank 2.5 12 Calculate Relative Standing with Statistical Functions Same Salary RANK.AVG RANK.EQ PERCENTRANK INC vs. PERCENTRANK EXC functions Some functions calculate values between 2 numbers ◦ e.g. PERCENTRANK calculates between 0% and 100% INC means calculate values between the 2 numbers including the 2 numbers EXC means calculate values between the 2 numbers excluding the 2 numbers Examples: ◦ PERCENTRANK.INC includes 0% and 100% ◦ PERCENTRANK.EXC excludes 0% and 100% 14 Example of Percentrank PERCENTRANK.INC PERCENTRANK.EXC 15 Example of Rank and Percentrank 16 2 Types of Relative Standing Functions (continued) 2) Get the Value for a particular relative standing PERCENTILE functions identify the value at the kth percentile ◦ ◦ ◦ Example: the 50th percentile salary is 46,363 QUARTILE identify the value at a specific quartile ◦ ◦ Quartiles 1, 2, and 3 are the 25th, 50th, and 75th percentile Example: the 1st quartile salary is 43,750 17 Example of Percentile and Quartile 18 19 Measuring Central Tendency 22 Measuring Central Tendency Population—dataset containing all the data to be evaluated Sample—smaller, more manageable segment of the population Variance—measure of a dataset’s dispersion Standard deviation—measure of how far the data sample is distributed around the mean 23 Measuring Central Tendency 24 Measure Central Tendency Variance functions: ◦ VAR.P—calculates the variance based on the population =STDEV.P(number1,number2) ◦ VAR.S—calculates the variance based on a sample =STDEV.S(number1,number2) Measure Central Tendency STDEV.S VAR.S More Complex Statistical Functions Analysis ToolPak ─ an add-in that contains tools for performing complex statistical analyses ◦ The Analysis ToolPak must be enabled, then the tools can be selected as usual ◦ Tools include Correlation, T-Tests, etc. 27 The correlation coefficient (a value between -1 and +1) tells you how strongly two variables are related to each other. - A correlation coefficient of +1 indicates a perfect positive correlation. As variable X increases, variable Y increases. As variable X decreases, variable Y decreases. 28 Measure Central Tendency CORREL—determine the strength of a relationship between two variables =CORREL(array1,array2) CORREL COPYRIGHT © 2017 PEARSON EDUCATION, INC. FREQUENCY— determines the frequency distribution of a dataset, Determine how often a set of number appears in specific range ◦ =FREQUENCY(Data_array,Bins_array) FREQUENCY 30 Creating a Histogram Histogram—visual display of tabulated frequencies ◦ Requires bins to tabulate the data and returns a frequency distribution table 31 Creating a Histogram 32
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

ANSWERS1 Question 3 ptsFrom
the "Educator Assessment" project file "e08h1Assessment", first
worksheet. What would be the result of Average Salary of teachers from
Veigo Township who have the salary greater than $45,000? (answer in two
decimal places) 54772.92Excel Code: =AVERAGEIFS(C3:C52,C3:C52,">45000",D3:D52,"Veigo")2 Question 3 ptsFrom
the "Educator Assessment" project file "e08h1Assessment" on the first
worksheet. What would be the 3rd Quartile EXC of Salary of all teachers?
(answer with one or two decimal places) 57278.5Excel Code: =QUARTILE.EXC(C3:C52,3)3 Question 3 ptsFrom
the "Educator Assessment" project file "e08h1Assessment", first
worksheet "Educator Assessment". What would be the Rank EQ of Salary of
teacher name Matsumoto from Jackson Township(who have the lower salary)?
(answer in number)35Excel Code:=RANK.EQ(C29,C3:C52,0)

High School Educator Information
Last Name
Kato
Han
Yoon
Lopez
Yamamoto
Lee
Garcia
Thomas
Gao
Cruz
Cruz
Ortiz
Rodriguez
Ma
Young
Jhadav
Jayaraman
Sanchez
Li
He
Mehta
Hamade
Bhatnagar
Takahashi
Young
Park
Matsumoto
Abdoo
Diaz
Rodriguez
Ito
Banerjee
Matsumoto
Kadam
Patil
Sasaki
Robinson
He
Lin
Thomas
Kimura
Jackson
Smith
Williams

Hire Date
12/22/2007
10/20/2015
4/23/2014
7/30/1999
12/29/2002
4/16/2014
4/23/2000
10/2/2002
7/16/2006
8/6/2011
9/11/2006
8/16/2005
8/4/2008
1/21/2010
5/4/2010
8/3/2006
10/14/2003
5/30/2...

Similar Content

Related Tags