DEVELOP A COMPLEX SPREAD SHEET MODEL TO MEET
PARTICULAR NEEDS (P2).
In this task, I will be developing that will be used to track BTEC student’s
enrolment and grades. I should develop complex spread sheet model that meets
user requirements.
Requirements.
Spread should include above requirements to meets users needs like Students names, students id
Courses , Grades they scored, multiple worksheets (with links), complex formulae e.g. at least two-step
process; large data sets cells linkage data entry forms e.g. menu systems, list boxes, drop-down boxes,
event controls; data validation error trapping lookup tables nested IF functions cell protection.
,The following are the requirements which I have included in developing complex spreadsheet:
Complexity in spreadsheet model can arise when there is combination of factors likemultiple pages,
complex formula, larger data sets and cell linkage between two sheet in excel. In order to make it
simple to the user, We need to make complex spreadsheet model which includes data entry forms, data
validation, nested if statements, error trapping and other which improves the quality of spreadsheet.
Multiple worksheets.
Instead of having single worksheet, you may design spreadsheet model which includes several separates
worksheets since there are number of benefits of linked worksheets.
You can simplify the development of large complex models by breaking down them in to series
of differents dependant work sheets. This will helps a lot since smaller worksheets are easier to
change, they don’t require too much memory and they are easier to open and close.
You can enter all data into one or more worksheets and then create differents views. For
example by creating reports which have links to relevant information
For example, in the spread sheet model, I created multiple work sheets to differentiate information of
students from each subject or unit. This helps easily the user to get releavant information which is
searching for. I created worksheets for each units like worksheets of computer system unit, information
system, database design, computer networks and IT support, this helps user easily to find information
for specifics subjects.
Worksheets for
different courses
,Complex formulae.
These are equations which perform calculations on values in excel sheet. For
example, the complex formular which I use is as follows;
The complex formular which I use above is for calculating the grade of each
student in a subject. It returns Distinctions if the student pass all the task in a unit.
It returns “merit” if student achieve all pass grade and merits grade on tasks. Also
returns “Pass” if the student achieve only Pass tasks and it returns “unclassified”
If the student didn’t even achieve pass grade in tasks. Generally, complex
formular helps easily to perform calculations in excel.
Large data sets.
Large data sets might involve using large number of excel sheets. With large data
sets user will need help in interpreting this data. In large data sets, by
summarizing the data for example by using pivot tables, this will helps user to see
data more clearly. For example, as you can see above, I have created large data
sets in my spread sheet model.
, Cell linkage.
Cell linkage is useful when when data of single spreadsheet model is kept in
various excel sheets, it is necessary that a formular in one excel sheet will require
data from cell or range of a cell in another worksheet. That’s why there is a facility
to links cell between worksheet which enable to create a complex formulae. By
linking cells between excel sheets, changes in one worksheet can have effects on
data in releavant cell cell which is linked to.
For example, In spreadsheet model which I have created, I did cell linkage
between each course sheets to consolidate grading sheet. This helps once data
such as grade changed on course sheet, also changes also will made on linked cell
in another sheet.
Cell linkage.
PARTICULAR NEEDS (P2).
In this task, I will be developing that will be used to track BTEC student’s
enrolment and grades. I should develop complex spread sheet model that meets
user requirements.
Requirements.
Spread should include above requirements to meets users needs like Students names, students id
Courses , Grades they scored, multiple worksheets (with links), complex formulae e.g. at least two-step
process; large data sets cells linkage data entry forms e.g. menu systems, list boxes, drop-down boxes,
event controls; data validation error trapping lookup tables nested IF functions cell protection.
,The following are the requirements which I have included in developing complex spreadsheet:
Complexity in spreadsheet model can arise when there is combination of factors likemultiple pages,
complex formula, larger data sets and cell linkage between two sheet in excel. In order to make it
simple to the user, We need to make complex spreadsheet model which includes data entry forms, data
validation, nested if statements, error trapping and other which improves the quality of spreadsheet.
Multiple worksheets.
Instead of having single worksheet, you may design spreadsheet model which includes several separates
worksheets since there are number of benefits of linked worksheets.
You can simplify the development of large complex models by breaking down them in to series
of differents dependant work sheets. This will helps a lot since smaller worksheets are easier to
change, they don’t require too much memory and they are easier to open and close.
You can enter all data into one or more worksheets and then create differents views. For
example by creating reports which have links to relevant information
For example, in the spread sheet model, I created multiple work sheets to differentiate information of
students from each subject or unit. This helps easily the user to get releavant information which is
searching for. I created worksheets for each units like worksheets of computer system unit, information
system, database design, computer networks and IT support, this helps user easily to find information
for specifics subjects.
Worksheets for
different courses
,Complex formulae.
These are equations which perform calculations on values in excel sheet. For
example, the complex formular which I use is as follows;
The complex formular which I use above is for calculating the grade of each
student in a subject. It returns Distinctions if the student pass all the task in a unit.
It returns “merit” if student achieve all pass grade and merits grade on tasks. Also
returns “Pass” if the student achieve only Pass tasks and it returns “unclassified”
If the student didn’t even achieve pass grade in tasks. Generally, complex
formular helps easily to perform calculations in excel.
Large data sets.
Large data sets might involve using large number of excel sheets. With large data
sets user will need help in interpreting this data. In large data sets, by
summarizing the data for example by using pivot tables, this will helps user to see
data more clearly. For example, as you can see above, I have created large data
sets in my spread sheet model.
, Cell linkage.
Cell linkage is useful when when data of single spreadsheet model is kept in
various excel sheets, it is necessary that a formular in one excel sheet will require
data from cell or range of a cell in another worksheet. That’s why there is a facility
to links cell between worksheet which enable to create a complex formulae. By
linking cells between excel sheets, changes in one worksheet can have effects on
data in releavant cell cell which is linked to.
For example, In spreadsheet model which I have created, I did cell linkage
between each course sheets to consolidate grading sheet. This helps once data
such as grade changed on course sheet, also changes also will made on linked cell
in another sheet.
Cell linkage.
