|
KILMALLOCK
COLLEGE |
|
|
|
|
|
|
Clear merge
and centre format and insert two new columns (Date of Birth; Age in Days) as
illustrated and enter the data.
Calculate the Age in Days for each student using the Today function and individual Dates of
Birth of students.
Move the Table so that it is in the range A5:D7
Insert a new column after Surname. Use the label Student Category. Use a nested if to generate the following
output:
Students older than 7665 days =
"Mature Student"; Students between 6569
and 7665 (inclusive) days = "U21"; Student under 6570 days = "U18".
|
|
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
| Date: |
25/11/2002 |
|
|
Note regarding ages |
|
|
Age (yrs) |
Age(Days) |
|
| Code |
AC |
CA |
HC |
|
18 |
6570 |
=L5*365 |
| Course |
Arts
& Crafts |
Computer
Applications |
Hotel
& Catering |
|
21 |
7665 |
=L6*365 |
| Course Fee (Euro) |
900 |
1200 |
1050 |
|
'=IF(D10>7655,"Mature
Student",IF(D10>6569,"U21","U18")) |
|
=(J10-K10)-(J10-K10)*L10 |
|
|
| Note:
Headings are on row 9 |
|
|
=IF(AND(G10="U21",J10>1000),20%,IF(AND(G10="U18",J10>1000),30%,0)) |
|
|
| Enrolment
Date |
Enrolment Year |
Date of Birth |
Age in Days |
First Name |
Surname |
Student
Category |
Course Code |
Course Name |
Course Fee (Euro) |
Reduction |
Special Reduction |
Net Fee |
|
| 10/09/2000 |
2000 |
19/05/1980 |
8225 |
Sean |
Murphy |
Mature
Student |
AC |
Arts & Crafts |
900.00 |
180.00 |
0% |
720.00 |
|
| 10/09/2000 |
2000 |
20/08/1975 |
9959 |
Mary |
Barry |
Mature
Student |
AC |
Arts & Crafts |
900.00 |
180.00 |
0% |
720.00 |
|
| 10/09/2000 |
2000 |
20/09/1963 |
14311 |
Catherine |
Kelly |
Mature
Student |
AC |
Arts & Crafts |
900.00 |
180.00 |
0% |
720.00 |
|
| 08/09/2001 |
2001 |
15/09/1945 |
20890 |
Olive |
O Donnell |
Mature
Student |
CA |
Computer Applications |
1200.00 |
0.00 |
0% |
1200.00 |
|
| 08/09/2001 |
2001 |
12/09/1983 |
7014 |
Gerry |
Doyle |
U21 |
CA |
Computer Applications |
1200.00 |
0.00 |
20% |
960.00 |
|
| 08/09/2001 |
2001 |
04/07/1982 |
7449 |
Ann-Marie |
Murphy |
U21 |
CA |
Computer Applications |
1200.00 |
0.00 |
20% |
960.00 |
|
| 08/09/2001 |
2001 |
07/07/1980 |
8176 |
Michael |
Curtis |
Mature
Student |
CA |
Computer Applications |
1200.00 |
0.00 |
0% |
1200.00 |
|
| 08/09/2001 |
2001 |
08/09/1971 |
11401 |
Cavan |
Carroll |
Mature
Student |
HC |
Hotel & Catering |
1050.00 |
0.00 |
0% |
1050.00 |
|
| 15/09/2002 |
2002 |
04/09/1987 |
5561 |
Patricia |
Eastwood |
U18 |
HC |
Hotel & Catering |
1050.00 |
0.00 |
30% |
735.00 |
|
| 15/09/2002 |
2002 |
07/09/1950 |
19072 |
Teresa |
Ryan |
Mature
Student |
HC |
Hotel & Catering |
1050.00 |
0.00 |
0% |
1050.00 |
|
| 15/09/2002 |
2002 |
04/09/1986 |
5926 |
Therese |
Hennessy |
U18 |
CA |
Computer Applications |
1200.00 |
0.00 |
30% |
840.00 |
|
| 15/09/2002 |
2002 |
05/08/1949 |
19470 |
Paul |
O Brien |
Mature
Student |
CA |
Computer Applications |
1200.00 |
0.00 |
0% |
1200.00 |
|
| |
|
|
|
|
|
|
|
|
|
|
| |
|
|
|
|
|
|
|
Total Net Fees: |
|
11355.00 |
|
| |
|
|
|
|
|
|
|
|
Average Net Fees: |
|
946.25 |
|
| |
|
|
|
|
|
|
|
|
|
Count of Participants: |
|
12 |
|
|
|
|
|
|
|
Insert a Special
Reduction column as shown and use a nested IF with
AND function to calculate these reductions according to the following
criteria:
U21 Category students and who pay a course fee of more than
1000 euro get a 20% reduction;
U18 Category students and who pay a course fee of more than 1000 euro get a 30%
reduction;
There is no Special Reduction for Mature Students (as they have already
got a reduction).
|
|
|
Recalculate the Net Fee taking the
Special Reduction into account and
recalculate the Total Net Fees, Average Net Fees, Count of
Participants. Format all numbers to
two decimal places except for Count of
Participants. Save as College2.xls
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|