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: bataan@deped.gov.ph
 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.

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:

1
 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.
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
 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!

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

3
 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
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)

4
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.

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.

5
1. ____________________________ 6. _______________________________ 2.
____________________________ 7. _______________________________
3. ____________________________ 8. _______________________________
4. ____________________________ 9. _______________________________
5. ____________________________ 10._______________________________

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

6
#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 tools in creating
derivative ICT content to effectively present data or information
related to specific professional tracks.

What’s New

7
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._______________________________

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

8
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 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.

9
 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)

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.

10
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.)

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:

11
 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.

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

12
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: _____________________
4. G3: ______________________ 10. C12: _____________________
5. B9: ______________________ 11. B13: _____________________
6. C9: ______________________ 12. C13: _____________________

What I Have Learned

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

13
1. Used to get the total of all the values from the
range of cells. A. AVE
2. Used to get the least value from the range of B. AVERAGE
values.
C. COUNTIF
3. Used to get the average value from the range of
values. D. IF
4. Used to get the greatest value from the range of E. MAX
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.
J. –
9. This signify range of values.
10. In the formula K. :
=IF(D6>=1000,”Spender”,”Saver”), what is the L. Saver
output if the condition is true?
M. Spender

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.

14
 Formulas:

1. Written Work Average: _____________________________________

2. Performance Task Average: _________________________________
3. Final Grade: _______________________________________________
4. Remarks: __________________________________________________

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.

15
 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

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

16
 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

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.

17
 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.

References

18
n.d. "What is Gross Profit?" Corporate Finance Institute. Accessed July 8,
2020.
https://corporatefinanceinstitute.com/resources/knowledge/accoun
ting/gross-profit/.

19
For inquiries or feedback, please write or call:

Department of Education – Region III,

Schools Division of Bataan - Curriculum Implementation Division
Learning Resources Management and Development Section (LRMDS)

Provincial Capitol Compound, Balanga City, Bataan

Telefax: (047) 237-2102

Email Address: bataan@deped.gov.ph

20