See Exam2answers.Xlsx for Spreadsheets

COMP 101 Exam 2 Answers

Thursday, 18 November 2010

(See Exam2Answers.xlsx for spreadsheets)

1. (Total: 7 points) There are Boolean values in A1, B1 and C1. Write a formula that is TRUE if

(a) (1 point) All values are true

=AND(A1,B1,C1)

(b) (1 point) At least one value is true

=OR(A1,B1,C1)

Two possible ways to do it:

=NOT(OR(A1,B1,C1)) (It is not the case that at least one value is true)

=AND(NOT(A1),NOT(B1),NOT(C1)) (All are false)

(d) (3 points) At least two values are true

Three possible ways to do it:

(1)Using AND and OR – If A1 is TRUE and EITHER B1 or C1 are TRUE -- OR –- if B1 and C1 are both TRUE

=OR(AND(A1,OR(B1,C1)),AND(B1,C1))

(2)Using AND and OR – pairwaise checking

=OR(AND(A1,B1),AND(B1,C1),AND(A1,C1))

(3) Count how many are TRUE (Can use this same technique in part c as well)

=COUNTIF(A2:C2,TRUE)>=2

2. (Total: 10 points) There is a text string in A1 that has at least two words, where a word is a character string and words are separated by blanks. There is no punctuation to worry about. The string may have leading and trailing blanks.

(a) (1 point) Write a formula that will get rid of the extra blanks.

=TRIM(A1)

(b) (3 points) You may now assume that B1 contains the value computed in part a (that is, no leading or trailing blanks). You are now to write a formula that extracts the first word from B1.

=LEFT(B1,FIND(" ",B1)-1)

(c) (2 points) Now let’s assume that the string may contain only one word. Write a formula that determines if the text string in B1 is a single word or is multi-word. Specifically, it is to print “SINGLE WORD” or “MULTI-WORD”.

=IF(ISERROR(FIND(" ",B1)),"SINGLE WORD","MULTI-WORD")

(d) (4 points) You are now to write a formula that properly returns the first word of A1 whether it has one or more words.

B1: =TRIM(A1)

C1: =LEFT(B1,FIND(" ",B1)-1)

D1: =IF(ISERROR(FIND(" ",B1)),B1,C1)

Or you can put it in a single cell:

=IF(ISERROR(FIND(" ",TRIM(A1))),

TRIM(A1),

LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1))

Or two options using IFERROR:

=IFERROR(LEFT(TRIM(A1),FIND(" ",TRIM(A1))-1),TRIM(A1))

=LEFT(TRIM(A1),IFERROR(FIND(" ",TRIM(A1))-1),LEN(TRIM(A1)) (Take the characters from the left. If you find a blank, stop one before it. If you don’t, take it all.)

3. (Total: 6 points) You are putting together a budget for a tournament with 20 teams, each with a different number of players. The budget has 7 different categories, such as food, transportation, housing, t-shirts. The first few rows and columns of your spreadsheet look like:

A / B / C / D / E
1 / Food / Housing / Transportation
2 / Cost per player / 45 / 100 / 75
3 / Team / Number of Players
4 / Brilliant Bananas / 12 / =B4*C2
5 / Crafty Corn Dogs / 15

You have written the first formula for cell C4. Since you need the same function in every cell that computes a budget item, you would like to simply copy cell C4 across all 7 categories and down all 20 teams.

(a) (4 points) Rewrite the formula to allow it to be dragged into all rows and columns.

=$B4*C$2

(b) (2 points) Explain the changes that you made and why you made them.

The first reference is for the number of players (B2). No matter which column I am in, the number of players will be column 2 of that row. I therefore want the column to stay fixed while the row changes (for the different teams).

Similarly, the second number is for the cost per player of that category. No matter what row I am in, the per player cost will be in row 2 of that column. I therefore want the row to stay fixed while the column changes (for the different categories).

4. (3 points) Explain the difference between using copy-paste and copying by formula and when you want to use each. Be specific as to the types of applications of spreadsheets in which each is appropriate. Illustrate how you would copy by formula.

When I use copy-paste, I do not keep the second field synchronized with the first: that is, changes in the first field are not reflected in the second. This is a problem when I have data that is apt to change, for example a grade sheet that will change for a different class or a different semester. This is not a problem if I am analyzing a set of data once and will not use the spreadsheets again. If I do need to keep the the data synchronized, I should use a simple copy function such as =A1.

5. (Total: 24 points) You are given a list of students and the number of credits that they have completed. You are asked to convert the number of credits completed to the class year. Credits will be a positive integer (no 0’s, no .5 credits). Specifically, you should use the following mapping:

Number of Credits Class Name Year

1-30 Freshman 1

31-60 Sophomore 2

61-90 Junior 3

91 or more Senior 4

There are many ways that you can do this. You are to do it THREE different ways: using VLOOKUP, using conditional statements, and using only mathematical functions. You should NOT worry about invalid inputs.

(a) (2 points) Create a table to be used by VLOOKUP in the following space (type directly into the table):

A / B / C
1 / Credits / Name
2 / 0 / Freshman
3 / 31 / Sophomore
4 / 61 / Junior
5 / 91 / Senior
6
7

(b) (4 points) Assume that the table built in part (a) is on a worksheet titled Classes. Using that table, write the VLOOKUP command that you would use to convert the number of credits in cell C4 to a class name.

=VLOOKUP(C4,Classes!$A$2:$B$5,2,TRUE) (With non-exact values, VLOOKUP will find the entry closest to value without going over it – like the Price is Right!)

=IF(C4<=30,

"Freshman",

IF(C4<=60,

"Sophomore",

IF(C4<=90,

"Junior",

"Senior")))

(d) (6 points) Finally, write a formula using NO conditional statements and no lookups that will convert the number of credits to the year, represented as an integer. (Hint: search for mathematical functions related to rounding.)

=MIN(CEILING(C4/30,1),4)(I didn’t require the MIN function. That covers the case where the person has more than enough credits to graduate, but for this purpose, we’ll call them a senior.)

(e) (6 points) Assuming that the number computed in part (d) is in cell D4, write an Excel formula that will translate that number into the correct class name. (Hint: Remember the function search is your friend. There are functions available that make this quite easy. Of course, you are free to make it as hard as you want…)

Here are 3 ways to do it. There are many more.

Brute force IF statement:

=IF(D4=1,

"Freshman",

IF(D4=2,

"Sophomore",

IF(D9=4,

"Junior",

"Senior")))

A function that we talked about:

=INDEX(Class!$B$2:$B$5,D4)

And yet another Excel function (which is actually more useful when you are choosing different functions to evaluate):

=CHOOSE(D4,"Freshman","Sophomore","Junior","Senior")