# Computer Science 12

# Week 5

### Sunday, March 1, 2020

- Assignment 5 (due next week)
- Excel tutorials
- Tutorials on problem-solving: math word problems
- Scratch tutorials, and overview of algorithmic problem-solving
- Study guide for next week's quiz

### Assignment 5 (due next week)

- Paper on problem-solving, part 1 (due next week, March 8)
- Paper on problem-solving, parts 2 and 3, preliminaries
- Excel assignment (due next week, March 8)
- Scratch assignment (due next week, March 8)

__Paper on problem-solving, part 1__(due next week)__Paper on problem-solving, parts 2 and 3, preliminaries____Excel assignment__(due next week):- Continuing the version of the invoice assignment we did TWO weeks ago (Assignment 3, NOT Assignment 4), for a store which sells only taxable items (thus, we do NOT need to use an IF function to compute tax as we did last week):
`last-first-invoice-5.xlsx`

- Create a spreadsheet listing at least 10 sales people who work for some particular company, to compute their rank as employees. The spreadsheet should have columns for the salespersons' names, the date that they were hired, the number of years (accounting years) they have worked for the company (computed from the date hired), the dollar amount of sales by each sales person during the past month, and the sales person's rank. The rank (3 being the highest, 1 being the lowest, is defined as follows: Sales people have rank 3 if they have worked for the company for at least five years and sold at least $20,000 worth of goods during the past month. They have rank 2 if they have either worked for the company for at least five years or sold at least $20,000 during the past month. They have rank 1 if neither of the above is true. Format the spreadsheet as a table.
`last-first-salesforce-5.xlsx`

__Scratch assignment__(due next week):`last-first-hide-seek.sb2`

`last-first-create-story.sb2`

`last-first-pong-game.sb2`

Part 1 of your paper on problem-solving is due next week. See the instructions posted for Week 2.

This week's Excel problems are much more complicated than last week's problems. Chances are, developing the needed formulas and testing them will take quite a bit of work. You will need to break each problem down into simpler parts and gradually work your way up to the complete solution.

Take detailed notes on your personal process of figuring out how to solve each problem. Then, having figured it out, write -- in your own words -- a step-by-step tutorial on how to figure out how to solve problems of this kind, using this week's Excel problems as examples. Next week, after you've written such a tutorial for each of this week's Excel problems, these tutorials will become parts 2 and 3 of your paper on solving problems in Excel. Draft 2, containing parts 1, 2, and 3, will be due three weeks from now. It is strongly recommended that you get at least a good start on writing the tutorials this week, while this week's Excel problems -- and the discussion of them in class -- are still fresh on your mind.

To help you think in-depth about problem-solving, review the Week 1 tutorials on Excel and solving math word problems, and then look also at this week's tutorials on Excel and problem-solving in programming. (Although Excel is not a programming language *per se*, Excel formulas do have a lot in common with programming.)

When reading each of the Excel tutorials, look first at the example problems and try to figure out the correct formulas yourself before reading the solution in the tutorial.

Not all the techniques described in these tutorials will necessarily work well for you, but you should experiment with a variety of problem-solving techiques. If you learn any valuable techniques from any of these tutorials, try to keep track of what you learned from where, so you can credit your sources in your paper.

Between the row containing the cell for the original subtotal and the row containing the cell for for the tax, add two more rows containing cells for (1) a discount (to be based on the subtotal) and (2) the discounted subtotal (i.e. the subtotal minus the discount).

The discount is to be computed as follows: (1) No discount for the first $100. (2) For subtotals above $100, five percent of the amount that the subtotal exceeds $100. (3) For subtotals above $500, the afore-mentioned discount plus an additional five percent of the amount that the subtotal exceeds $500. The discount should be applied to the subtotal of all products (not the per-product total price).

The discount must be computed using a single formula that will work correctly regardless of how big the subtotal is. All decision-making about the discount, based on the size of the subtotal, must be done automatically by the formula (using the IF function), NOT by you, the human user.

You should have separate cells at the bottom for (1) the original subtotal, (2) the discount, (3) the discounted subtotal (i.e. the original subtotal with discount subtracted), (4) the sales tax, and (5) the total (which will be the sales tax added to the discounted subtotal, not the original subtotal).

It is recommended that you make a first draft of this spreadsheet using a simpler discount formula than the one described above. After you have successfully tried it with a simpler formula, experiment with more complex formulas, gradually building up to the formula described above.

As before, the invoice should have a filename with the following format:

where "last" and "first" should be replaced by your own last and first name.

Your invoice must be unique, containing a store name, product names, and prices different from those in files submitted by other students in Computer Science 12.

In a separate cell outside the table, at or near the top of the page, put a date that will be used in computing the number of years an employee has worked for the company. consider carefully what function (e.g. DATE or TODAY) is most appropriate to use in generating that date.

The computation of rank is complicated. Try to break the computation of rank into simpler parts before you put together the entire formula for the rank.

This spreadsheet should have a filename with the following format:

We will use this file again in future exercises both in lab and in homework.

Submit BOTH of the above files via email by no later than the beginning of next week's class, and make sure you also have access to the files during lab on that day. The two files must be sumitted as file attachements to a single email message. Please submit both at once, not in separate email messages. The email message should have a subject line like the following:

HW 5 Excel, LastName FirstName

where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively.

** Warning:** You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

Do the following three tutorials that are a part of Scratch itself: "Hide-and-Seek Game", "Create A Story", and "Create a Pong Game". (The tutorials are listed in a column on the right side of the Scratch window after you click "Tips" in the menu at the top left.) See Notes on the Scratch tutorials for some clarifications to each tutorial, plus an "additional step" that you will be required to do, after each tutorial, as part of this homework assignment.

Save your work in files with appropriate filenames, as follows:

where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial plus the "additional step" given in Notes on the Scratch tutorials.

Before you submit your homework, please delete any unused sprites, variables, and scripts.

Then send all three files attached to ONE email with the following subject line:

HW 5 Scratch, LastName FirstName

where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

** Warning:** You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

To all information for Week 5 **|** To all homework assignments

### Excel tutorials

**On IF and other logical functions (AND, OR, NOT):**

- AND function and OR function - Microsoft
- Using IF with AND, OR and NOT functions - Microsoft
- Excel's AND, NOT, and OR Functions by Michele McDonough
- Excel AND and OR Functions by Ted French on Thoughtco.com

Note: Although Excel has a ** NOT** function, chances are you'll only very rarely need it. You can usually write a simpler formula without

**. For examples, see Eliminating unnecessary**

`NOT`

**. functions in Notes on logic in Excel.**

`NOT`

**On nested IF functions:**

- Fabulous Examples of Excel Functions: Nested IF Functions - Keynote Support
- Nested IF function example - ExcelJet
- Use nested functions in a formula - Microsoft
- MS Excel: How to use the Nested IF Functions (WS)

**Some important notes about nested IF:**

- There are other ways to combine
functions in a formula besides nesting one inside the other. For example, for some problems, the most appropriate formula is one which does arithmetic with the values computed by two or more distinct (non-nested)`IF`

functions. Nesting`IF`

functions may or may not be the most appropriate way to combine them, depending on the problem.`IF`

- Some of the tutorials listed on this page mention that Excel 2016 has introduced a new function,
intended as an easier-to-use replacement for nested`IFS`

,in some cases. Do NOT use the`IF`

function in homework this week or on next week's quiz. We will discuss`IFS`

in class next week. Some of the other tutorials also mention`IFS`

which we will not be discussing until Week 10.`VLOOKUP`

,

**Problem-solving with nested IF functions:**

- How to Write a Nested IF Statement in Excel by Matthew Kuo
- 19 tips for nested IF formulas - ExcelJet
- How to use nested IF statements in Excel with AND, OR, NOT on "Experiments in Finance"
- IF function - nested formulas and avoiding pitfalls - Microsoft
- Using multiple IF statements in Excel - fiveminutelessons

**More about problem-solving:**

See also the Week 1 tutorials on solving math word problems and this week's tutorials on algorithmic problem-solving skills.

**Date functions review:**

See the Excel tutorials listed for Week 3.

To all information for Week 5 **|** To all tutorials about Excel

### Tutorials on problem-solving: math word problems

**Solving math word problems:**

The following tutorials review the skills needed to solve math word problems. These same skills are needed when writing Excel formulas, and also when writing programs (including very short, simple programs of the kind you've been exposed to in this course).

- Math Forum: When I'm given a 'word problem', where do I start?
- Purplemath: Translating Word Problems: Keywords
- StudyRight: How to solve math word problems without giving yourself a headache
- for dummies: Simple steps for solving word problems

Note: You will need to develop, through practice, the methodologies that work best for you personally. Some of the above tutorials contradict each other on various issues. Experiment with different approaches and use what works best for you.

To all information for Week 5 **|** To all tutorials about miscellaneous topics

### Scratch tutorials, and overview of algorithmic problem-solving

__This week's Scratch tutorials:__:__Overview of algorithmic problem-solving skills:__:- Slate: How to Teach Yourself About Algorithms. General introduction to the concept of an algorithm.
- Ryan's Tutorials: Problem Solving Skills. Excellent, clearly-written tutorial on problem-solving in general, not just in the context of programming.
- Designing The Algorithm -- part of a tutorial on C, but this page requires no knowledge of any programming language. (Warning: Although this page contains some good advice, it also contains quite a few typos, including some missing words.)
- Problem Solving and Algorithms -- top third of page only. (Stop when you reach the heading "Example 4.1: Pick and Plant," below which the page covers many topics beyond the scope of this course. Note: the "Hypothesis" in the last sentence above that heading, "Perhaps every area formula involves multiplying two measurements," is FALSE -- can you think of a counter-example?)

We will again be using some of Scratch's built-in tutorials, listed on the right hand side of your Scratch window when you click "Tips" in the menu at the top left. This week we will be using the following tutorials: "Hide-and-Seek Game", "Create A Story", and "Create a Pong Game".

See also Notes on some Scratch tutorials, especially the notes on "Hide-and-Seek Game", "Create A Story", and "Create a Pong Game".

The following tutorials discuss the most important skills needed to write programs in any language. These same skills are also useful when writing relatively complicated Excel formulas, e.g. formulas using multiple IF functions.

See also this week's tutorials on solving math word problems.

To all information for Week 5 **|** To all tutorials about programming (very basic intro)

### Study guide for next week's quiz

On Sunday, March 8, we will have our usual brief (20 minutes or so) quiz at the beginning of class. (Please arrive on time or early.) It will be an on-paper quiz with Excel problems somewhat similar to last week's Excel quiz problem, but probably more challenging.

To prepare for the Excel part of the quiz, you will need practice using all the functions we've covered so far (listed in the previous two study guides), plus AND and OR. To that end, it is recommended that you not only do the homework but also play with examples in the Excel tutorials for all five weeks up to now. Make sure, especially, that you get practice with IF, AND, and OR functions, including nested IF functions and other possible combinatons of IF functions. You will likely also be expected to know how to use date functions (DATE, TODAY, YEARFRAC, and DAYS360) and how to compute the difference in actual days between dates via simple subtraction. All other Excel functions and other Excel features that have been covered so far will be fair game too.

There will also be one or more multiple-choice and/or fill-in-the-blanks questions. Likely Excel topics covered include the Excel date functions and the difference between absolute and relative cell references.

Besides Excel, there will be some fill-in-the-blanks and/or multiple choice problems involving Scratch. Know the Scratch-specific meanings of the terms "sprite", "costume", "script", "block", "block palette", and "variable," and be prepared to answer basic questions about specific blocks similar to the ones you used in the assignmnets during the first five weeks. Review all the exercises you've done so far, and make a note of the various blocks you have used under various categories of the block palette. In your Scratch window, examine and play with a wide variety of blocks in each category, enough to give yourself a memorable feel for (1) what each category is all about, and (2) which kinds of blocks belong in each category.

To all information for Week 5 **|** To all quiz and exam study guides