Empowerment

Technologies
Quarter 1 – Module 4:
Advanced Excel Formulas
Empowerment Technologies – Grade 11
Alternative Delivery Mode
Quarter 1 – Module 4: Advanced Excel Formulas
First Edition, 2020

Republic Act 8293, section 176 states that: No copyright shall subsist in any work of the
Government of the Philippines. However, prior approval of the government agency or office wherein
the work is created shall be necessary for exploitation of such work for profit. Such agency or office
may, among other things, impose as a condition the payment of royalties.

Borrowed materials (i.e., songs, stories, poems, pictures, photos, brand names, trademarks,
etc.) included in this module are owned by their respective copyright holders. Every effort has been
exerted to locate and seek permission to use these materials from their respective copyright owners.
The publisher and authors do not represent nor claim ownership over them.

Published by the Department of Education

Secretary: Leonor Magtolis Briones
Undersecretary: Diosdado M. San Antonio

Development Team of the Module

Writer: Cherryl D. Cordova

Editor: Geraldine A. Pinoy
Reviewer: Paulo F. De Guzman
Illustrator: Cherryl D. Cordova
Layout Artist: Cherryl D. Cordova
Cover Design: LRMDS-Bataan

Management Team:
Schools Division Superintendent : Romeo M. Alip, PhD, CESO V
OIC-Asst. Schools Division Superintendent : William Roderick R. Fallorin
Chief Education Supervisor, CID : Milagros M. Peñaflor, PhD
Education Program Supervisor, LRMDS : Edgar E. Garcia, MITE
Education Program Supervisor, AP/ADM : Romeo M. Layug
Education Program Supervisor :
District Supervisor :
Division Lead Book Designer : Jenelyn D.Rivero
District LRMDS Coordinator : Pepito B. Hernandez
School LRMDS Coordinator : Cherryl D. Cordova
School Principal : Edgardo Jesus S. Basa II
District Lead Layout Artist :
District Lead Illustrator :
District Lead Evaluator :

Printed in the Philippines by Department of Education – Schools Division of Bataan

Office Address: Provincial Capitol Compound, Balanga City, Bataan
Telefax: (047) 237-2102
E-mail Address: [email protected]
 Empowerment
Technologies
Quarter 1 – Module 4:
Advanced Excel Formulas
Introductory Message
For the facilitator:

Welcome to the Empowerment Technologies – Grade 11 Alternative Delivery

Mode (ADM) Module on Advanced Excel Formulas.

This module was collaboratively designed, developed and reviewed by

educators both from public and private institutions to assist you, the teacher or
facilitator in helping the learners meet the standards set by the K to 12 Curriculum
while overcoming their personal, social, and economic constraints in schooling.

This learning resource hopes to engage the learners into guided and
independent learning activities at their own pace and time. Furthermore, this also
aims to help learners acquire the needed 21st century skills while taking into
consideration their needs and circumstances.

In addition to the material in the main text, you will also see this box in the
body of the module:

Notes to the Teacher

This contains helpful tips or strategies that will help you in guiding the learners.

As a facilitator you are expected to orient the learners on how to use this
module. You also need to keep track of the learners' progress while allowing them
to manage their own learning. Furthermore, you are expected to encourage and
assist the learners as they do the tasks included in the module.

1
For the learner:

Welcome to the Empowerment Technologies – Grade 11 Alternative Delivery

Mode (ADM) Module on Advanced Excel Formulas.

This module deals with the fundamental and most commonly used advanced
formulas in spreadsheet software like Microsoft (MS) Excel. MS Excel is the most
commonly used spreadsheet software not only by businesses but by other fields too.
There are a lot of advanced techniques that you can apply to make your computation
much easier and manageable. The pre-requisite of this topic is that you have the
background and know already the basic in MS Excel.

This module was designed to provide you with fun and meaningful
opportunities for guided and independent learning at your own pace and time. You
will be enabled to process the contents of the learning resource while being an active
learner.

This module has the following parts and corresponding icons:

What I Need to Know This will give you an idea of the skills or
competencies you are expected to learn in the
module.

What I Know This part includes an activity that aims to

check what you already know about the
lesson to take. If you get all the answers
correct (100%), you may decide to skip this
module.

What’s In This is a brief drill or review to help you link

the current lesson with the previous one.

What’s New In this portion, the new lesson will be

introduced to you in various ways such as a
story, a song, a poem, a problem opener, an
activity or a situation.

What is It This section provides a brief discussion of

the lesson. This aims to help you discover
and understand new concepts and skills.

What’s More This comprises activities for independent

practice to solidify your understanding and
skills of the topic. You may check the
answers to the exercises using the Answer
Key at the end of the module.

What I Have Learned This includes questions or blank

sentence/paragraph to be filled in to
process what you learned from the lesson.

2
 What I Can Do This section provides an activity which will
help you transfer your new knowledge or skill
into real life situations or concerns.

Assessment This is a task which aims to evaluate your

level of mastery in achieving the learning
competency.

Additional Activities In this portion, another activity will be given

to you to enrich your knowledge or skill of the
lesson learned. This also tends retention of
learned concepts.

Answer Key This contains answers to all activities in the

module.

At the end of this module you will also find:

References This is a list of all sources used in developing

this module.

The following are some reminders in using this module:

1. Use the module with care. Do not put unnecessary mark/s on any part of the
module. Use a separate sheet of paper in answering the exercises.
2. Don’t forget to answer What I Know before moving on to the other activities
included in the module.
3. Read the instruction carefully before doing each task.
4. Observe honesty and integrity in doing the tasks and checking your answers.
5. Finish the task at hand before proceeding to the next.
6. Return this module to your teacher/facilitator once you are through with it.
If you encounter any difficulty in answering the tasks in this module, do not
hesitate to consult your teacher or facilitator. Always bear in mind that you are
not alone.

We hope that through this material, you will experience meaningful learning
and gain deep understanding of the relevant competencies. You can do it!

3
 What I Need to Know

This module was designed and written for you to maximize the advanced
application techniques in creating ICT derivative content using common
productivity tool such as MS Excel.

At the end of this module, you are expected to:

1. Identify the different parts of MS Excel environment and the different

functions used to solve complex problems.
2. Use common productivity tools effectively by maximizing advanced application
techniques. (CS_ICT11/12-ICTPT-Ic-d4)
3. Create an original or derivative ICT content to effectively communicate or
present data or information related to specific professional tracks.
(CS_ICT11/12-ICTPT-Ic-d5)

What I Know

MULTIPLE CHOICE: Select the best answer from the choices and write the letter
that corresponds to your answer in your notebook.

1. Which of the following is the correct format in writing the range of cells?
A. E1;E8 B. E1,E8 C. E1:E8 D. E1..E8

Refer to the worksheet below to answer the questions from item number two
(2) to seven (7).

A B C D E
1 List of Amount per Total Total Total
Allowances Day Allowance Amount Amount
per Week Spent per Saved per
Week Week
2 Food 150 350
3 Transportation 50 200
4 Medical 200 700
5 School 100 350
6 Overall Total
7
8 Minimum Total Amount Saved
Per Week

4
 9 Maximum Total Amount Spent
Per Week
10 Total Number of 350 amount
spent per week

2. [Total Amount per Week] is computed as [Amount per Day] multiplied by 7. Which
of the following formulas are you going to type in cell C2?
A. = A2 x 7 B. = B2 * 7 C. = B2 x 7 D. = A2 * 7

3. [Total Amount Saved per Week] is deducting [Total Amount Spent per Week] from
[Total Allowance per Week]. Which of the following formulas are you going to type
in cell E2?
A. = D2 – C2 B. = D2 – B2 C. = C2 – B2 D. = C2 – D2

4. [Overall Total Allowance per Week] is adding all the [Total Allowance per Week].
Which of the following formulas are you going to type in cell C6?
A. = SUM(C2:C5) B. = SUM(A2:A5) C. = SUM(C2;C5) D.= SUM(C2,C5)

5. [Overall Total Amount Saved per Week] is adding all the [Total Amount Saved per
Week]. Which of the following formulas are you going to type in cell E6?
A. = SUM(C2:C5) B. = SUM(E2:E5) C. = SUM(C2;C5) D.= SUM(C2,C5)

6. To be typed in cell C8, which of the following formulas is used to get the
minimum total amount saved per week?
A. = MIN(E2:E5) B. = MIN(E2,E5) C. =MIN(E2;E5) D. = MIN(E2..E5)

7. To be typed in cell C9, which of the following formulas is used to get the
maximum total amount spent per week?
A. = MAX(E2:E5) B. = MAX(B2:B5) C. = MAX(C2:C5) D.= MAX(D2:D5)

8. It is used to get the total of all the values from the range of cells.
A. AVERAGE B. MAX C. MIN D. SUM

9. It is used to get the least value from the range of values.

A. COUNT B. MAX C. MIN D. SUM

10. It is used to get the average value from the range of values.
A. AVERAGE B. MAX C. MIN D. SUM

11. It is used to get the greatest value from the range of values.
A. COUNT B. MAX C. MIN D. SUM

12. It is used to add all cells that meet certain criteria.

A. COUNTIF B. MAXIF C. MINIF D. SUMIF

13. It is used to count cells that meet certain criteria.

5
 A. COUNTIF B. MAXIF C. MINIF D. SUMIF

14. It is used to prevent cell references from adjusting when it is copied to another
cell.
A. / B. % C. $ D. &

15. In the formula =IF(D6>=1000,”Spender”,”Saver”), what is the output if the

condition is true?
A. Saver B. Spender C. System Error D. None

What’s In

A. Identify the commonly used parts in MS Excel pointed in each number.

1. 6.
2. 7.
3. 8.

6
 4. 9.
5. 10.

B. Identify the basic formulas used to solve the given problem for each
number.

#1. What is the formula to get the total allowance for Day 1 (without using function)?

#2. What is the formula to get the total allowance for Day 2?

#3. What is the formula to get the total food allowance for six days if Day 1 food
allowance is used?
#4. What is the formula to get the total fare allowance for six days if Day 2 fare
allowance is used?
#5. What is the formula to get the overall total allowance for six days?

Notes to the Teacher

This module aims to prepare the students to know how to use the MS Excel as one of the productivity

7
 What’s New

Below is a worksheet for your budget plan for a week. Write the appropriate
formula used in the colored cells.

2 6

3 4 5 7

10

Formula:
1. 6.
2. 7.
3. 8.
4. 9.
5. 10.

8
 What is It

Microsoft Excel, the Microsoft’s spreadsheet software is considered the

industry standard piece of software in data analysis and happens to be one of the
most preferred software by investment bankers and financial analysts in data
processing, financial modelling and presentation (Corporate Finance Institute, 2019).
It is also used in education and small businesses to make computations easier. If
you hate mathematics because of numbers and you’re head is aching because of
computing, then there are techniques you can do in Excel to make it easier.

It is important to know the two basic ways to perform calculations in Excel:

Formulas and Functions. A formula is an expression that operates on values in a
range of cells or a cell while function is a predefined formula.

Simple Formula

Take a look at the worksheet below.

To get the 250 for [Total Allowance per Week], multiply [Days per Week] and
[Amount per Day]. So the formula to be typed in cell C2 is =E7 * B2. B2 is the cell
that contains the value for [Amount per Day] for Food. E7 is the cell that contains
the value for [Days per Week] which is 5. If you will copy the formula to cell C3, it
will be changed to =E8 * B3, where cell E8 is empty and will give a result of 0. Change
the formula in cell C2 to =$E$7 * B2. And copy it to cell C3. The cell reference E7 did

9
not change when you copied it to another cell because of the $ symbol. The $ signs
in the formulas are simply to prevent these references from adjusting.

To get 150 for the [Overall Total] for Amount per Day, the formula to be
typed in cell B6 can be =50+40+20+40. But the problem in this formula is that once
you change your budget for food, the [Overall Total] remains the same. The
technique to avoid that problem is to use the cell reference in the formula like
=B2+B3+B4+B5. But what if you will add another item, the formula will get longer.
The advanced technique you can do is to use functions.

Function is a predefined formula with a friendly name. It reduces lengthy

manual entry of formulas. For example, =B2+B3+B4+B5+B6+B7 is a formula to get
the sum. And the function equivalent to this formula is =SUM(B2:B7).

Advanced Formulas

1. SUM( )
Change the formula in cell B6 to =SUM(B2:B5). B2:B5 is the range of cells,
which means you will start adding the value from cell B2 to cell B5.
The SUM() function is used to get the sum of all the values.It has a
format of =SUM(range of values).
Example: =SUM(C2:C5).

2. MIN( )
To get 50 for the [Minimum Amount saved per week], use the formula
=MIN(E2:E5).
The MIN() function is used to get the minimum value from the range of values.
It has a format of =MIN(range of values).
Example: =MIN(C2:C5)

3. MAX( )
To get 200 for the [Maximum Amount spent per week], use the formula
=MAX(D2:D5).
The MAX() function is used to get the maximum value from the range of values.
It has a format of =MAX(range of values).
Example: =MAX(C2:C5)

4. AVERAGE( )
To get 97.5 for the [Average Amount spent per week], use the formula
=AVERAGE(D2:D5).
The AVERAGE() function is used to get the average(arithmetic mean) value of
its arguments. It has a format of =AVERAGE(range of values).
Example: =AVERAGE(C2:C5)

10
5. COUNTIF( )
If you want to determine the [Total Number of Item with 40 budget], in cell
E11, enter the formula =COUNTIF(B2:B5, 40). The criteria you set is 40. It means
that you are looking the value 40 from cell B2 to cell B5.
The COUNTIF() function is used to count all cells that meet certain criteria.
The format is =COUNTIF(range, criteria).
Example: =COUNTIF(B2:B5, 50) counts the number of cells with 50 from
cells B2 to B5.

6. SUMIF( )
SUMIF adds all cells that meet certain criteria. Like, if you want to add all
cells that have greater than 50 amount you saved in a week. The formula is
=SUMIF(E2:E5,50). Enter this formula in cell E13.
The SUMIF() function is used to add all cells that meet certain criteria.
The format is =SUMIF(range, criteria).
Example: =SUMIF(E2:E5, “>50”) adds all the cells with greater than 50 in
cells E2 to E5. Just enclose the criteria in open and close quotation marks.

7. IF( )
You can also find out your [Personality Trait] based on the total amount you
spent for the week. If you spent more than 500 in a week then you are spender,
otherwise you are a saver. To convert this into formula, in cell E12, enter the formula
=IF(D6>500,”Spender”,”Saver”).
The IF() function is used to check whether a condition is met, and returns one
value if TRUE, and another value if FALSE. The format is
=IF([logical_test],[value_if_true],[value_if_false].
Example: =IF(D6>=1000,”Spender”,”Saver”) means if the value in cell D6 is
greater than or equal to 1000, the value “Spender” is returned, otherwise return a
“Saver”.

Other MS Excel Features that you can use.

Flash Fill

Flash Fill automatically fills in values. It is located at the Data ribbon as

shown in the figure below. For example, you will fill up cells B3:B5 the same
amount in cell B2. Delete the values in cells B3:B5. Make cell B3 as the active cell
then go to the Data ribbon, and click it Flash Fill. (Note: Return the original data.)

11
 Chart

A chart also called as graph is a tool to visually present data in different chart
formats such as Bar, Pie, Line, Area, Doughnut, or Radar charts. You can easily
create it. For example, you’re going to present your allowance for a day in a bar
chart format. Refer to the figure below and follow the given instructions:

1. Select cells A1:B5.

2. Go to the Insert ribbon, look for the Charts tab, click Insert Column Chart,
and choose 3D column. The chart is now created.
3. You can add or modify the chart elements such as adding Legends by clicking
the + icon located at the upper right corner of the chart.
4. Save your work.

12
 What’s More

Use MS Excel in your laptop or spreadsheet app in your smart phone to

easily type in the formula. But if you don’t have MS Excel you can write the correct
formula in a piece of paper.

Given your grade worksheet below. Compute the average for each subject
and for every quarter. The remarks is PASSED if the computed Average is greater
than or equal to 75, otherwise the remarks is FAILED. Count the total number of
subjects with grade greater than 84. Determine also the highest and lowest grade in
each quarter. Fill in the colored cells with the needed formulas.

Note: Once you entered the formula in cell F2 (the starting cell), you can
copy it and paste it up to cell F9. Same with the other formulas.

Formulas for the following cells:

1. F2: 7. B11:
2. F3: 8. C11:
3. G2: 9. B12:
10. C12:
4. G3:
11. B13:
5. B9: 12. C13:
6. C9:

13
 What I Have Learned

Matching Type: Match the description in column A with its name in column
B.

Column A Column B
1. Used to get the total of all the values from A. AVE
the range of cells.
B. AVERAGE
2. Used to get the least value from the range of
values. C. COUNTIF
3. Used to get the average value from the range D. IF
of values.
4. Used to get the greatest value from the range E. MAX
of values. F. MIN
5. Used to check whether a condition is met.
G. SUM
6. Used to add all cells that meet certain criteria.
7. Used to count cells that meet certain criteria. H. SUMIF
8. Used to prevent cell references from adjusting I. $
when it is copied to another cell.
9. This signify range of values. J. –
10. In the formula K. :
=IF(D6>=1000,”Spender”,”Saver”), what is the
L. Saver
output if the condition is true?
M. Spender

14
 What I Can Do

Given the worksheet below with student’s record. Compute the grade by
getting the 25% of the average grade in written work plus 50% of the average
grade in performance task plus 25% of the grade in periodical test. Remarks is
PASSED if the Final Grade is greater than or equal to 74, otherwise FAILED.

Formulas:

1. Written Work Average:

2. Performance Task Average:
3. Final Grade:
4. Remarks:

15
 Assessment

MULTIPLE CHOICE: Select the best answer from the choices and write the
letter that corresponds to your answer in your notebook.

1. It is used to get the total of all the values from the range of cells.
A. AVERAGE B. MAX C. MIN D. SUM

2. It is used to get the least value from the range of values.

A. COUNT B. MAX C. MIN D. SUM

3. It is used to get the average value from the range of values.

A. AVERAGE B. MAX C. MIN D. SUM

4. It is used to get the greatest value from the range of values.

A. COUNT B. MAX C. MIN D. SUM

5. It is used to add all cells that meet certain criteria.

A. COUNTIF B. MAXIF C. MINIF D. SUMIF

6. It is used to count cells that meet certain criteria.

A. COUNTIF B. MAXIF C. MINIF D. SUMIF

7. It is used to prevent cell references from adjusting when it is copied to another

cell.
A. / B. % C. $ D. &

8. In the formula =IF(D6>=1000,”Spender”,”Saver”), what is the output if the

condition is true?
A. Saver B. Spender C. System Error D. None

9. Which of the following is the correct format in writing the range of cells?
A. E1;E8 B. E1,E8 C. E1:E8 D. E1..E8

16
 Refer to the worksheet below to answer the questions from item number ten
(10) to fifteen (15).
A B C D E
1 List of Amount per Total Total Total
Allowances Day Allowance Amount Amount
per Week Spent per Saved per
Week Week
2 Food 150 350
3 Transportation 50 200
4 Medical 200 700
5 School 100 350
6 Overall Total
7
8 Minimum Total Amount
Saved Per Week
9 Maximum Total Amount
Spent Per Week
10 Total Number of 350
amount spent per week

10.[Total Amount per Week] is computed as [Amount per Day] multiplied by 7. Which
of the following formulas are you going to type in cell C2?
A. = A2 x 7 B. = B2 * 7 C. = B2 x 7 D. = A2 * 7

11.[Total Amount Saved per Week] is deducting [Total Amount Spent per Week] from
[Total Allowance per Week]. Which of the following formulas are you going to type
in cell E2?
A. = D2 – C2 B. = D2 – B2 C. = C2 – B2 D. = C2 – D2

12.[Overall Total Allowance per Week] is adding all the [Total Allowance per
Week]. Which of the following formulas are you going to type in cell C6?
A. = SUM(C2:C5) B. = SUM(A2:A5) C. = SUM(C2;C5) D.= SUM(C2,C5)

13.[Overall Total Amount Saved per Week] is adding all the [Total Amount Saved
per Week]. Which of the following formulas are you going to type in cell E6?
A. = SUM(C2:C5) B. = SUM(E2:E5) C. = SUM(C2;C5) D.= SUM(C2,C5)

14. To be typed in cell C8, which of the following formulas is used to get the
minimum total amount saved per week?
A. = MIN(E2:E5) B. = MIN(E2,E5) C. =MIN(E2;E5) D. = MIN(E2..E5)

15. In the formula =IF(D6>=1000,”Spender”,”Saver”), what is the output if the

condition is true?
A. Saver B. Spender C. System Error D. None

17
 Additional Activities

1. Create a simple financial statement of your small business (What is Gross

Profit? n.d.). Please check the figures from the table below as your reference.
Show historical results starting from 2017-2019.

For computation, refer to the following mathematical

formulas: Gross Profit = Sales Revenue – Cost of Goods Sold
Earnings Before Tax = Gross Profit – Total Expenses
Net Earnings = Earnings Before Tax – Taxes

2. Create a Class Record with ten students in the list with the following data:

Written Work
 3 Quizzes
 2 Seat works
 2 Home works
Performance Tasks

 4 Activities
Periodical Test

To compute the Final Grade, add the 25% of written work average, 50% of
performance tasks average, and 25% of periodical test.

18
Answer Key

19
References

n.d. "What is Gross Profit?" Corporate Finance Institute. Accessed July 8,

2020.
https://corporatefinanceinstitute.com/resources/knowledge/accoun
ting/gross-profit/.

20
For inquiries or feedback, please write or call:

Department of Education – Region III,

Schools Division of Bataan - Curriculum Implementation Division Learning Resources Management and
Provincial Capitol Compound, Balanga City, Bataan Telefax: (047) 237-2102
Email Address:

21