More formulae and functions - Exercises - UCL



UCL

Education & information support division

information systems

Excel 2003

Logic

Exercises

Document No. IS-021-v2

Content

Task 1 - Validation 1

Task 2: Conditional formatting 1

Task 3: IF statements 2

Task 4: Statistical IFs 2

Task 5: COUNTIF and SUMIF 3

Task 6: AND, OR, NOT 3

Task 7: Nested IFs 3

Task 8: Lookup 4

Task 9: Lookup and RANK function 4

Task 10: HLookup 5

Task 11: VLookup 6

Task 12: Additional exercise 6

Training files

If you wish to attempt the following exercises and you are not using a training account it is necessary to download the training files used in this workbook from the IS Training web site at: ucl.ac.uk/is/training/exercises.htm

Full instructions on how to do this are provided on this web page.

The downloaded files will be copied to a folder on the r:\ drive (unless other wise specified) into the r:\training.dir\excel\more-formulae-functions folder.

Task 1 - Validation

1. Open the workbook Validation exercise.xls.

1. Set a validation rule in cell A3 to only accept a date after 01/09/2006 (i.e. greater than or equal to).

a) Create a message that explains that only a date can be entered in the cell.

b) Add a Warning that will appears when incorrect data is entered with the title “Date check” and the message “The date should be after 1 Sept 2006”

2. Make B3 a text cell with a maximum of 50 characters.

a) Add an Input message with a title “Course Title” and the message “Abbreviate if necessary - maximum character = 50”

b) Set the Error Alert to Stop with a title “Course Title” and the message “Too many characters”

3. Set up a drop down list in cell C3 of 1, 2, or 3. (Key this information into the Source box.)

a) Set the Error Alert to Information, with a title “Room number”, and the message “Only use rooms 1, 2, or 3.”

4. Set up a drop down list in cell D3 to refer to the list of trainers in cells P2:P7. Only allow users to select from this list. Put a Stop message with the title “Trainer”, and the message “Please select a trainer from the list offered”.

5. Set a Stop message if there are less than 4, or more than 12 trainees entered in cell E3.

a) Enter the following input message “Courses can only be run if there are at least 4 trainees, with a maximum of 12”.

b) Put a Stop message with the title “Trainees”, and the message “Must be between 4 and 12”.

6. Enter the following data into row 3:

|01/08/2006 |Advanced Excel - Importing data and sharing workbooks |"4" |Your name |2 |

▪ Does Excel allow you to enter 01/08/2006? What message did you receive?

▪ Could you enter the full course title in cell B3? Abbreviate as below and try again.

▪ Was it possible to enter room number 4 in cell C3? Set the room number to Room 1 instead.

▪ Was it possible you type in your own name in cell D3? Select any name from the list instead.

▪ Was it possible to run the course with 2 participants? Set this figure to 6 instead.

|01/08/2006 |Adv Excel - Importing data and sharing workbooks |"1" |Pravin Saheed |6 |

7. Save the workbook as Task 4a.xls and close it.

Task 2: Conditional formatting

1. Open the Club.xls workbook.

2. Apply conditional formatting to highlight any members who joined the club after 1990 in blue.

3. Apply conditional formatting to highlight members, whose names occur before “R” in the alphabet, using a yellow background for the cells.

4. Check that the conditional formatting has worked, and then save and close the file.

5. Open the Exercise conditional formatting.xls workbook

6. Apply Conditional Formatting to cells B4:M4 in the Total Income row to display, in a blue font, any cells that are less than £1000.

7. In the Total Expenses row (cells B15:M15), apply a background colour to any cells which have a total greater than or equal to £1000.

8. In the Monthly Saving row (cells B16:M16), apply a pattern to indicate all monthly savings above £1000, and also display monthly savings below £500 in a different way.

9. Change the amount in cell J2 to £850.00 and watch to see if the formatting in any of the cells change.

10. Save the workbook as Exercise 3A and close it.

Task 3: IF statements

The IF Function

1. On a blank workbook, format the cells of column C to currency using the £ symbol.

2. In cell B1 enter the text Calculating Interest.

3. In cell B3 enter the text Balance.

4. In cell B4 enter the text Interest.

5. Enter any figure in cell C3 for your bank balance.

6. The interest will depend on whether your balance is over or under £500. Use the Insert Function to enter the following function in C4:

=IF(C3 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download