CSE200 SP09 KREEVES                                  QUIZ#2                              SEAT# ____________

 

NAME _______________________________________________        Lecture:  TR 1:30-3:18pm 

 

Lab section (check one):            _______ F 1:30-3:18pm             ________ F 3:30-5:18pm

 

Instructions:

·    Filling out the correct seat# and lab section on both the test and the answer sheet is worth 2 points.

·         Put away all books, papers, and calculators. 

·     Turn off all beepers and cell phones.

·     Read each question carefully and fill in the answer in the space provided.  Answers must be legible or they will be marked incorrect.  If there are multiple answers to choose from, please CIRCLE the correct answer. The question will not be graded at all if there are multiple answers to choose from.

·     When time has run out you will be told to put all pens/pencils down. 

·     Be sure to use values as determined by previous problems and do not use values from problems that have not yet been solved per the ordering of the questions.

·     Use cell references whenever possible.

·     Don’t use a $ if NOT copying                            

·     Only use the functions given.

·         Do NOT put quotes around Boolean values.

·     Your answer should update correctly when additional input data is added to the problem or when input data is changed (unless where specifically noted otherwise).

 

 

 

 

 

CSE200 SP09 KREEVES                                  QUIZ#2                              SEAT# ____________

 

NAME _______________________________________________        Lecture:  TR 1:30-3:18pm 

 

Lab section (check one):            _______ F 1:30-3:18pm             ________ F 3:30-5:18pm

 

 

Text Box: SCRATCH AREA
 


Okay if put extra/optional worksheet name on cell reference even though solution going in current worksheet.

 

1.      (6 pts) Write an Excel formula in cell Q2input!G3, which can be copied down to Q2input!G21, to determine if the player’s name starts with the letter J.

 

=AND(A3>="J",A3<"K")

Optional $ on column

 

2.      (6 pts) Write an Excel formula in cell Q2team!D2, which can be copied down to Q2team!D10, to determine the number of players on the team that play position number 1.

 

=COUNTIF(Q2input!B$3:B$21,A2)

Optional $ on column

 

3.      (6 pts) Write an Excel formula in cell Q2team!C13, which can be copied down to Q2team!C14, to determine the number of players that play infield. NOTE: A position is considered to be an infield position if there is a TRUE in the range Q2team!C2:C10 as specified by Q2team!B13; the position is an outfield position if the value in the range Q2team!C2:C10 is FALSE as specified by Q2team!B14.

 

=SUMIF(C$2:C$10,B13,D$2:D$10)

Optional $ on column

 

4.      (6 pts) Write an Excel formula in cell Q2input!H3, which can be copied down to cell Q2input!H21, to determine if john’s batting average is above the team’s batting average.

 

=D3>AVERAGE(D$3:D$21)

=D3>SUM(D$3:D$21)/COUNT(D$3:D$21)

Can use columns B or C instead of D on the COUNT function

Optional $ on column

 

5.      (6 pts) Write an Excel formula in cell Q2input!I3, which can be copied down to cell Q2input!I21, to determine if john will change leagues. A player will change leagues if they are in the major league with a batting average less than the team’s batting average (i.e. they will be demoted to the minor league) or a player will change leagues if they are in the minor league and they have a batting average greater than the team’s batting average (i.e. they will be promoted to the major league). NOTE: The above problem has already determined the player’s batting average in comparison to the team’s batting average. Be sure to use the above result for this problem instead of retyping the above solution.

 

=OR(AND(E3,NOT(H3)),AND(F3,H3))

E3 options – E3=TRUE, NOT(F3), F3=FALSE

NOT(H3) options – H3=FALSE

F3 options – F3-TRUE, NOT(E3),E3=FALSE

H3 options – H3=TRUE

 

6.      (6 pts) Write an Excel formula in cell Q2input!I22 (not shown) to determine (true or false) if only left fielders (they are position number 7) have to change leagues.  The data in column B will not change for this problem.

 

=AND(OR(I15,I19),NOT(OR(I3:I14,I16:I18,I20:I21)))

Can say =TRUE for each cell reference in the first OR

Can say =FALSE for each of the cell references instead of NOT/OR

Can NOT say something like AND(I3:I14=FALSE) incorrect excel syntax

No $ allowed since not copying

 

7.      (6 pts) Write an Excel formula in cell Q2 input!J3, which can be copied down to cell Q2 input!J21, to determine an estimate of how long in seconds it will take for john to run the bases if he hits a home run. NOTE: You will find the necessary data to solve this problem on the Q2units! worksheet, however, the starting point for this problem is the value in Q2input!C3 which says that john can run 100 meters in 10.5 seconds.

 

=C3/100/Q2units!A$3*Q2units!A$1*Q2units!A$2

Optional $ on column

Order optional but mathematical operator must go in front of designated cell reference

 

8.       (6 pts) Write an Excel formula in cell Q2input!J22 (not shown) to determine (true or false) if everyone can run a homerun in less than 16 seconds. NOTE: Remember this solution must be updatable if additional players are added to the list i.e. an AND function is not going to work here.

 

=COUNTIF(J3:J21,">=16")=0

=COUNTIF(J3:J21,"<16")=COUNT(B3:B21)

Instead of count function, can use sum(Q2team!:D2:D10)

Count function can use column ranges C or D as well as column J

No $ allowed

 

Incorrect option: AND(J3<16,J4<16,…,J21<16) not updatable

Again, AND(J3:J21<16) is incorrect syntax

 

 

SCORE _____________/50