CS88 Midterm 2

Date and Time (Ch 6)

1. Write a formula which will always evaluate to tomorrow's date. Put this in A1.

   In A2, calculate the date which is 2 years, 5 months, and 5 days after the date in A1.

2. In A3, put the current time. In A4, add 5 hours, 6 minutes and 3 seconds to that time,
 using time functions.

   In A5, calculate the same, based on A3. But use arithmetic rather than
date/time functions.

Counting and summing (Ch 7, 14)

3. Make up a bunch of data (text). Using an array formula, count up how many cells
contain precisely 2 words.

(Recall that you can count words by taking the len of the cell, substituting nothing for
 space, taking the len again, and if the difference is 1, then there was one space in
the cell, and thus two words.)

4. Make up a bunch of data (numbers). Sum the numbers which are in
the range of 10 to 20.

5. Separate the numbers into a group of bins, using either FREQUENCY or a bunch of array formulas.

Deliberate circular references (Ch 16)

6. Generate a bunch of test scores. Then, using the max function, calculate the
highest ever maximum (all time high).

7. Solve the recursive equation; x = cos(x)
8.

1)
Hi
I regularly import data from another program with part numbers =
identified in coloum A as:
R/RVI/0444
R/RVI/5362
R/VOL/3699 etc.

Is there a way of interogating the full part number and displaying only =
the middle three letters and the last digits with out the / between

Thanks in anticipation

Martin

2)
Hi team..pls help me here.

I have an excel sheet with cells having text as shown below:

[India]Delhi is the capital of India.[USA]Washington is the capital of USA.[China]Beijing is the capital of China.[Singapore]Singapore is the capital of Singapore.

This complete text is in one single cell. Similar text is in other cells.

I want to segregate these contents into separate columns so that:

[India]Delhi is the capital of India ->  goes into one column

[USA]Washington is the capital of USA -> goes into another column.. and so on....

3)
I have a long column of numerical data with occassional random
occurences of text in a cell, like so:

25
37.5
48
25.9
57
NAM
65
59
24
etc.

I want to replace every occurrence of "NAM" (always the same text)
with the contents of the cell immediately above.
In the example above I want to replace NAM with 57.

4) Define a name "Database" for the range A1:G6 on Sheet1. You can use absolute references for this.

5) Define a name "OneDown" which refers to the cell immediately below you

6) Define a name "SurroundMinimum" which will give you the minumum *value* of the cells which are OneUp, OneDown, OneLeft, and OneRight. It might help to first define names for all of the above.

7) Define a name "SurroundMinimum" which will give you the *cell reference* of the cell with the minumum value of the cells which are OneUp, OneDown, OneLeft, and OneRight. What I mean by cell reference is that it is the actual cell, such that e.g. if I call the Row() function on it, it will tell me which row it is in.

8) Put the value "hello there how are you doing" in a cell. For the cell formatting, make it bold, red, with a Red Accent 2 fill, with wrap text enabled.

pig

igpay

1. get first letter

2. find out if that letter is a vowel

3. if vowel, just give the word & "way"

4. else, take off the first letter, give rest of word, & first letter, & "ay"

=LEFT(B9, 1)

=OR(B10="A", B10="E", B10="I", B10="O", B10="U")

=B9&"way"

=MID(B9,2,1000)&B10&"ay"

=IF(B11, B12, B13)

  

40638

1. get the year

2. treat it like text

3. get len

4. substitute 0's for blanks, get len

5. subtract second len from first len

6. if 2 or more, say hooray. Else, say boo!

=YEAR(A18)

=TEXT(A27, "@")

=LEN(A27)

=LEN(SUBSTITUTE(A27, "0", "") )

=A29-A30

=IF(A31>=2, "hooray", "boo")

Sample exam

Part I -- Lab Material
___________________

Questions like you will find in a mid-level or capstone exercise.
Amount of material: about two mid-level exercises worth.


Part II
____________________

Not this many questions, but to give you a sampling of types of questions.

1) Make a table with a list of (three) car models and their average highway and city-street mileage. In a separate part of your spreadsheet, have three cells where one fills in the car model, how many miles you need to travel, and what percentage will be on the highway. Based on that, calculate the number of gallons for the entire trip. Use Functions, Absolute, and Relative references where appropriate.

2) What is the R1C1 reference formula for a relative reference, one cell up and three cells to the right?

3) Name a range A1:B6, whose scope is limited only to Sheet1.

4) Let us say you have five word sentence in A1. Using as many cells as you want, calculate for me how many words are longer than 3 letters. I should be able to switch the sentence, and your formulas should still work.

5) The rules for Pig Latin:
http://en.wikipedia.org/wiki/Pig_Latin

My simple rules are as follows: Basically, if a word begins with a consonant, strip off that consonant, give me the rest of the word, and add 'ay' to the end. Thus, 'pig' will become 'igpay' and 'latin' will become 'atinlay'. If a word begins with a vowel, just add 'way' to the end. Thus, 'eye' will become 'eyeway'.

Iway ovelay otay peaksay inway igpay atinlay
is the Pig Latin of
I love to speak in pig latin

Write a formula, or a series of formulas, which will take a single word and make it into its Pig Latin equivalent.

6) Calculate a date which will be 2 years and 35 days from today. By today, I mean TODAY().

7) Write a formula which will tell me if the year of a specific Serial Number contains two zeros in it.

homework:
all the practice exercises in ch 3

in formulas book, we finished ch 3.
maybe start reading ch 4

in formulas book:
we finished ch 2
we will begin ch 3

in lab book:
HW:
ch 2, all the practice exercises

Now:
ch 2, the midlevel exercises

+ / * &

range operators
: - give me a range
, - give me union
 space bar - give me the intersection

Function IsLess(A as string, B as string) as boolean
  if a < b then
    isless = true
  else
    IsLess = false
  end if
End Function

1. take in the input
2. get its code
3. then add 32
4. then get its char

HW - not to collect, yet:
only perform this conversion when it is in the appropriate range. use char, code, if, and