One of the attributes of Microsoft Excel that make it unique is its builtin functions that have real life applications both in various fields of study and in business. Some of these builtin functions are:
• IF functions
• ARITHMETIC functions such as the SUM function, AVERAGE function, etc.
• FORECAST function
• TREND function
• COUNTIF function
• SUMIF function
• CONCATENATE functions
In this chapter, we will explicitly explain these functions with illustrations and real life applications. I will give you some practical exercises to test your knowledge.
This is the first part (Part 1) of this tutorial chapter (Chapter 3). I will illustrate the business applications of various builtin functions in building result system (grade list), payroll systems and how to apply subtotals to these real life systems.
This is the end of this part.
Now go over to the next part
of this tutorial chapter:
• IF functions
• ARITHMETIC functions such as the SUM function, AVERAGE function, etc.
• FORECAST function
• TREND function
• COUNTIF function
• SUMIF function
• CONCATENATE functions
In this chapter, we will explicitly explain these functions with illustrations and real life applications. I will give you some practical exercises to test your knowledge.
This is the first part (Part 1) of this tutorial chapter (Chapter 3). I will illustrate the business applications of various builtin functions in building result system (grade list), payroll systems and how to apply subtotals to these real life systems.
IF FUNCTION ANALYSIS
This is a logical function or conditional statement used to
evaluate a given condition. It returns the first variable if the condition is
true or met, otherwise it returns the second variable. If the condition is
false or not met.
Various application software have IF function as one of
their builtin functions with slight difference with this Excel IF function syntax.
The syntax is =IF(condition,[value_if_true],[value_if_false])
Enter
Where;
 Condition is the criteria set for excel to use for the evaluation of the records.
 value_if_true is the variable that will be returned if the condition is met.
 value_if_false is the variable that will be returned if the condition is not met. It is most times ignored.
NESTED IF FUNCTION
This is a special case of IF function whereby an IF function
is enclosed in another IF function. It is used when there are more than one
conditions to specify. For example, the worksheet in the figure below contains
a table for sales record. In the table, there is a Remark column to contain a remark for each sale.
If you want Excel to tell you whether you made a profit,
loss or you just broke even (I.e. no profit no loss) whenever you enter the
item, the quantity sold, the cost price and the selling price, then you will apply
a nested IF function in that column.
Place your cursor in cell D2 and type the following nested
IF function:
=IF(D2>C2,"profit",IF(D2<C2,"loss",IF(D2=C2,"break
even"))) press Enter.
Then use excel fill handle tool to generate a remark for the
subsequent cells in the Remark column.
PRACTICAL ILLUSTRATION/BUSINESS APPLICATIONS OF NESTED IF FUNCTION
I will illustrate the various reallife applications of
Arithmetic and Nested IF functions of MSExcel. Please try to practice these examples
on your own.
STUDENTS RESULT SYSTEM (GRADE LIST)
QUESTION:
Given that the grade system of Government Secondary School
Owerri is as follows:
0 to 39.9 = F
40 to 49.9 = E
50 to 59.9 = D
60 to 69.9 = C
70 to 79.9 = B
80 to 100 = A
Above 100 = Error
If 50 students participated in the examination of the
following courses:
Mathematics, English and Biology, using the above grade system,
calculate the following for each student:
1. Total score.
2. Average Score.
3. Grades on the respective subjects.
4. Overall grade.
5. Filter all female students that made an “A” in mathematics.
SOLUTION TO THE GRADE LIST SYSTEM QUESTION
You should first create a table like the one contained in the worksheet in the figure below.
Result System – Table 1

Type in the following formulas in the appropriate cells and use the hand fill tool to generate the results for the subsequent cells in that column:
1. For the TOTAL SCORE column, place your cursor in cell J3 and type the following formula:
=SUM(D3,F3,H3) press Enter.
2. For the AVERAGE SCORE column, place your cursor in cell I3 and type the following formula:
=J3/3 press Enter. Reduce the number of decimals to two.
3. For the ENGLISH GRADE column, place your cursor in cell E3 and type the following formula:
=IF(D3<40,"F",IF(D3<50,"E",IF(D3<60,"D",IF(D3<70,"C",IF(D3<80,"B",IF(D3<=100,"A",IF(D3>100,"ERROR"))))))) press Enter.
4. For the MATHEMATICS GRADE column, place your cursor in cell G3 and type the following formula:
=IF(F3<40,"F",IF(F3<50,"E",IF(F3<60,"D",IF(F3<70,"C",IF(F3<80,"B",IF(F3<=100,"A",IF(F3>100,"ERROR"))))))) press Enter.
5. For the BIOLOGY GRADE column, place your cursor in cell I3 and type the following formula:
=IF(H3<40,"F",IF(H3<50,"E",IF(H3<60,"D",IF(H3<70,"C",IF(H3<80,"B",IF(H3<=100,"A",IF(H3>100,"ERROR"))))))) press Enter.
6. For the OVERALL GRADE column, place your cursor in cell G3 and type the following formula:
=IF(F3<40,"F",IF(F3<50,"E",IF(F3<60,"D",IF(F3<70,"C",IF(F3<80,"B",IF(F3<=100,"A",IF(F3>100,"ERROR"))))))) press Enter.
The completed Grade List System is shown below.
Result System – Table 2

7. Create the three ranges of data. Type “Female” in the Sex column under the criteria range. Type “A” in the Mathematics Grade column under the criteria range.
Use the procedure for performing Advance filter to filter the database based on the specified criteria.
You might need to revisit chapter 2 for the procedure in advance filter.
PAYROLL SYSTEM DESIGN IN EXCEL
This is the compilation or list of workers’ information and their payment details in an organization or company. It is a database containing all workers information and their payment details such as basic salary, allowances, gross pay, and net pay.
DEFINITION OF SOME TERMS USED IN PAYROLL MANAGEMENT
1. PAY SLIP:
This is a detached leaflet or a record from the payroll that contains a worker’s information and payment details.
2. BASIC SALARY:
This is an amount or sum of money a worker is entitled to at the end of the month in a company or organization which is highly influenced by worker’s qualifications and level, and also by the strength of the organization.
3. ALLOWANCE:
This is a fringe benefit that is allocated to every worker in order to encourage workers and enhance their performance in the company or organization.
4. TAX:
This is a compulsory levy that is deducted from a worker’s basic salary at the end of the month in order to generate revenue for the state and federal government.
5. GROSS PAY:
This is the summation of or the total of a worker’s basic salary and all the allowances he is entitled to at the end of every month.
6. NET PAY:
This is Gross pay minus all deductions such as tax and loans. It is the final takehome of a worker at the end of the month.
PRACTICAL ILLUSTRATION OF A PAYROLL SYSTEM
QUESTION:
The manager of JOELINKS SERVICES wants to computerize the payroll system of the company. As a spreadsheet expert, design a payroll system to his recommendation and approval to pay his workers on monthly basis such that basic salary is influenced by qualification as follows:
WASSCE = $20,000
OND = $25,000
NCE = $28,000
HND = $32,000
BSc = $40,000
MSc = $50,000
PhD = $80,000 respectively.
Marital allowance would be 8% and 10% for single and married workers respectively. Transport, Feeding, Dressing and Housing allowances would be 6%, 4%, 5%, and 8% of their basic salaries respectively.
Compute a tax deduction such that:
WASSCE = 5%, OND = 6%, NCE = 8%, HND = 10%, BSc = 12%, MSc = 15% and PhD = 20% of their basic salaries respectively.
Calculate the Gross pay and Net pay for each worker.
Setup an advanced query or filter to filter all the married workers with qualification of BSc.
SOLUTION TO THE PAYROLL SYSTEM
You should first create a table like the one contained in the spreadsheet in the figure below:
Type in the following formulas in the appropriate cells and use the hand fill tool to generate the results for the subsequent cells in that column:
1. For the BASIC SALARY column, place your cursor in cell D3 and type the following formula:
=IF(C4="WASSCE",20000,IF(C4="OND",25000,IF(C4="NCE",28000,IF(C4="HND",32000,IF(C4="BSc",40000,IF(C4="MSc",50000,IF(C4="PhD",80000,0))))))) press Enter.
2. For the MARITAL ALLOWANCE column, place your cursor in cell E3 and type the following formula:
=IF(B3="Single",D3*8%,IF(B3="Married",D3*10%,0)) press Enter.
3. For the TRANSPORT ALLOWANCE column, place your cursor in cell F3 and type the following formula:
=D3*6% press Enter.
4. For the FEEDING ALLOWANCE column, place your cursor in cell G3 and type the following formula:
=D3*4% press Enter.
5. For the DRESSING ALLOWANCE column, place your cursor in cell H3 and type the following formula:
=D3*5% press Enter.
6. For the HOUSING ALLOWANCE column, place your cursor in cell E3 and type the following formula:
=D3*8% press Enter.
7. For the TAX column, place your cursor in cell J3 and type the following formula:
=IF(C3="WASSCE",D3*5%,IF(C3="OND",D3*6%,IF(C3="NCE",D3*8%,IF(C3="HND",D3*10%,IF(C3="BSc",D3*12%,IF(C3="MSc",D3*15%,IF(C3="PhD",D3*20%,0))))))) press Enter.
8. For the GROSS PAY column, place your cursor in cell K3 and type the following formula:
=SUM(D3:I3) press Enter.
9. For the NET PAY column, place your cursor in cell L3 and type the following formula:
=K3J3 press Enter.
The completed Payroll System is shown below.
Payroll System – Table 2

10. Create the three ranges of data. Type “Married” in the M Status column under the criteria range. Type “BSc” in the Qual column under the criteria range.
Use the procedure for performing Advance filter to filter the database based on the specified criteria.
You might need to revisit chapter 2  part 1 for the procedure in advance filter.
SUBTOTAL AND HOW TO APPLY IT IN AN EXCEL WORKSHEET
Subtotal is an Excel tool used to calculate the total value or amount spent in a particular group of records in a database. It is mainly used in Payroll and Budget Control Systems to calculate the total amount spent in each department of a company.
To Apply Subtotal to a Database in an Excel Worksheet:
1. Prepare and activate the worksheet containing the database you wish to use, for example, a payroll database.
2. Sort the records using the Department column as your sorting field by clicking Sort located in the Sort & Filter group in the Data tab. Select the sort column from the Sort By drop down menu and click OK as shown below.
Subtotal sort

3. Click on Subtotal located in the Outline group of Data tab. The subtotal dialogue box appears.
4. Specify the changing field from the AT EACH CHANGE IN drop down menu which in this case should be the Department column.
5. Specify the ADD SUBTOTAL TO field. In the case of a payroll system, specify either the Gross Pay or the Net Pay column depending on your intention.
6. Put a check the Replace current subtotals and Page break between groups boxes and click OK as shown below.
Subtotal dialogue

Excel does the calculation for you and returns the values in
a row below the last record in each group as shown below.
SOME SAMPLE QUESTIONS UNDER GRADE LIST SYSTEM AND PAYROLL SYSTEM
Please attempt these sample questions:
1. Government Secondary School Owerri wants to computerize her students result in the following subjects:
English, Mathematics, Chemistry, Physics, Biology, Economics, Literature and Government.
a.) As a Spreadsheet Manager, design a result System to compute the Total Score and Average Score.
b.) Using IF function, generate the grade letters for each subject and the overall grade using the following grade Scheme:
0 – 39.9 = Fail
40 – 49.9 = Pass
50 – 59.9 = Credit
60 – 69.9 = Upper Credit
70 – 100 = Distinction
c.) Generate 30 records and Set up an advance filter to filter Science Students from Imo State.
2. a.) Zenith Bank PLC wants to computerize her worker’s payroll system to compute the Basic Salary based on the following qualifications:
SSCE = $1200
NCE = $1300
HND = $1500
BSc = $1800
MSc = $2200
PhD = $2800
b.) Compute 8%, 12%, 7.5%, 6% and 5% of the Basic Salary as Housing, Transport, Medical, Feeding and Dressing allowances.
c.) Tax deduction should be calculated as follows:
SSCE = 1.5%
NCE = 2%
HND = 2.5%
BSc = 3%
MSc = 3.5%
PhD = 4%
d.) Calculate the Gross and Net Pay.
e.) Set up a subtotal to calculate the total amount spent in each department.
Recommended MS Excel Textbook
Get this book (Kindle format): Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016.
Click Here to know more about the book.
Now go over to the next part
of this tutorial chapter:
Get this book (Kindle format): Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016.
If this tutorial post helped you, then it will also help
your friends. Kindly click the one of the share buttons to share this post with
your friends.
Don’t forget to subscribe to get our latest tutorial post delivered to your email.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!