Additional Problem @ Forum

Week 1 (Problem @ Forum)

I'm posting an additional problem that you can work on. The problem description is below and you will also need the file week01-prob.ods. If you know the solution, you are welcome to post it here and explain how you solve it. The idea is that you learn from each other.

Problem Description

Column A contains a list of 1000 numbers. You want to know whether there are any numbers greater than 4 in the list. You can of course use conditional formatting, sorting or filtering. Instead, we would like you to use a formula to do this task.

Your task is to come out with a formula and place it in Cell D3. The formula should return “Yes” if there is at least a number greater than 4 in the range A1:A1000, otherwise it should return “No”.

There are many methods to solve this problem. One method is to use a combination of IF and COUNTIF. Another is to use a combination of IF and MAX. However, do not let me restrict your thinking. Any method that works is acceptable.

Note that I have defined the name “threshold” to refer to the number 4. You will see that if you click on cell D1. A good practice is to use these named constants in your formula because it makes the context clearer.

If you know how to do it, you can post your solution with your explanation in Piazza. The idea is that you learn from each other.

Some of you may ask why this is an interesting problem. For example, you need to transport an item from one place to another. The item must be kept at a temperature of no more than, say, 4 degrees Celsius. What you can do is to put an electronic device with the item. The device will take regular measurements of the temperature and store the measurements in the memory. When the item is delivered, you want to go through all the measurements to see whether any one of them exceeds 4 degrees Celsius.

---