CPT 105
Franklin College
Erich Prisner
CPT 105
Franklin College
Erich Prisner
The file contains data about an academic travel to Zuerich-Munich-Vienna. The grey areas of the sheet shouldn't be touched by yourself. Coulmn B indicates whether the option is already taken. Of course, in case of hotels, one has to choose one out of the options. The task is to prepare a sheet that shows the total cost of the trip in Sfr and also the average cost per student.
| 1 | Freeze the heading (between rows 3 and 4) of the sheet. |
| 2 | Display the average price per night for the Munich and Vienna hotels in cell D29. |
| 3 | Use conditional formatting to format all values in column D in red and bold face that are smaller than 5. |
| 4 | Format the values in columns D and E in Euro, and the values in columns F and G in Sfr. |
| 5 | In cells H4 to H11, display the totals for the Euro-zone hotels by multiplying number of nights by number of students by price per night. Make sure to not include the values but rather the references of the values. Also write the formula only once and copy and paste it to the other cells. |
| 6 | Do the same as in step number 4 for the Zuerich hotels into cells I4-I11. |
| 7 | in cells H17 to H27 display the total costs for the corresponding Euro events, and in cells I17 to I27 do accordingly for the Sfr events. |
| 8 | In column J, translate the values from column H into Sfr. |
| 9 | In column K, add the corresponding values from the same row in columns H and I. |
| 10 | In column L, use the IF formula to display the value of column K provided the option is chosen, meaning, provided there is a "1" in the corresponding row in column B. |
| 11 | In cell L28, display the sum of all values in column L. |
| 12 | In cell L29, display the average cost per student. |
After finishing, please save your file and send it as an email attachment at my email eprisner@fc.edu . Tell me if for some reason that doesn't work. In the worst case, you can also print your file and give me the printout.
Erich Prisner, November 2005