Microsoft Excel is a spreadsheet program that allows users to organize and calculate data across rows and columns in a workbook. It can perform calculations using functions and present information visually through charts and graphs. Key features include spreadsheets divided into rows and columns, the use of formulas and functions to perform calculations, and the presentation of information in different visual formats. Excel is useful for tasks like financial analysis, data visualization, and decision making.
The document discusses various Excel functions and formulas for performing calculations, including financial, logical, and date functions. It covers how to use the SUM, AVERAGE, IF, and other common functions. The document also provides guidance on using relative and absolute cell references, copying and pasting formulas, and the auto fill feature.
This document provides an overview and introduction to using Microsoft Excel. It explains key parts of the Excel interface like the title bar, menu bar, toolbars, and worksheet tabs. It also demonstrates how to enter formulas, functions, and logical formulas in Excel. Common functions discussed include SUM, AVERAGE, MEDIAN, and IF. The document is intended to familiarize new Excel users with the basic features and capabilities of the program.
Excel allows users to enter formulas to perform calculations on worksheet data. Formulas begin with an equal sign and can reference cell addresses to manipulate cell values using mathematical operators like addition and subtraction. Common functions like SUM, AVERAGE, and MAX simplify calculations. Formulas can be copied and filled using relative or absolute cell references.
1. Functions simplify formulas in Excel by performing calculations using cell references and predefined operations, rather than manually typing operations. Common functions include SUM, AVERAGE, MAX, and MIN.
2. Cell references in functions can be relative, absolute, or mixed. Relative references adjust when copied, absolute references stay fixed, and mixed references fix either the row or column.
3. Functions are copied using copy/paste or autofill, and cell references adjust depending on their type—relative references change, while absolute references stay the same.
Formulas in Excel begin with an equal sign and include cell references and operators. Functions are predefined formulas that perform calculations using specific cell values called arguments. Both formulas and functions can be copied and will adjust cell references depending on whether they use relative, absolute, or mixed references. Functions simplify formulas by using cell ranges and built-in calculations like SUM, AVERAGE, and TODAY.
The document outlines a 1-hour workshop on using Excel formulas and functions. It will introduce basic formulas using mathematical operators and functions like SUM. It will cover entering static and dynamic formulas, order of precedence, and using the AutoSum feature and functions to simplify formulas. Exercises will have students create simple formulas and use functions.
The document provides information on formulas and functions in Excel. It discusses entering formulas using cell references and arithmetic operators. Formulas typically use relative references. Functions are named operations that return values, like SUM to add a range of cells. Common functions include SUM, AVERAGE, COUNT, MIN, and MAX. The document demonstrates using the AutoSum button, absolute references, VLOOKUP and HLOOKUP functions, logical IF functions, and date functions. It also covers conditional formatting.
This document provides an overview of a training course on intermediate Microsoft Excel skills. It covers topics such as logical functions, financial functions, lookup functions, sorting and filtering data, working with dates and times, data validation, formula auditing, the Solver tool, data consolidation, pivot tables, and preparing data for analysis. The training is designed to help participants learn how to enter and navigate data, create formulas to solve problems, use charts and graphs, and reference data using relative and absolute cell references. The first lecture demonstrates how to refresh Excel skills, design better spreadsheets, and improve readability through cell styles and data validation.
The document discusses various Excel functions and formulas for performing calculations, including financial, logical, and date functions. It covers how to use the SUM, AVERAGE, IF, and other common functions. The document also provides guidance on using relative and absolute cell references, copying and pasting formulas, and the auto fill feature.
This document provides an overview and introduction to using Microsoft Excel. It explains key parts of the Excel interface like the title bar, menu bar, toolbars, and worksheet tabs. It also demonstrates how to enter formulas, functions, and logical formulas in Excel. Common functions discussed include SUM, AVERAGE, MEDIAN, and IF. The document is intended to familiarize new Excel users with the basic features and capabilities of the program.
Excel allows users to enter formulas to perform calculations on worksheet data. Formulas begin with an equal sign and can reference cell addresses to manipulate cell values using mathematical operators like addition and subtraction. Common functions like SUM, AVERAGE, and MAX simplify calculations. Formulas can be copied and filled using relative or absolute cell references.
1. Functions simplify formulas in Excel by performing calculations using cell references and predefined operations, rather than manually typing operations. Common functions include SUM, AVERAGE, MAX, and MIN.
2. Cell references in functions can be relative, absolute, or mixed. Relative references adjust when copied, absolute references stay fixed, and mixed references fix either the row or column.
3. Functions are copied using copy/paste or autofill, and cell references adjust depending on their type—relative references change, while absolute references stay the same.
Formulas in Excel begin with an equal sign and include cell references and operators. Functions are predefined formulas that perform calculations using specific cell values called arguments. Both formulas and functions can be copied and will adjust cell references depending on whether they use relative, absolute, or mixed references. Functions simplify formulas by using cell ranges and built-in calculations like SUM, AVERAGE, and TODAY.
The document outlines a 1-hour workshop on using Excel formulas and functions. It will introduce basic formulas using mathematical operators and functions like SUM. It will cover entering static and dynamic formulas, order of precedence, and using the AutoSum feature and functions to simplify formulas. Exercises will have students create simple formulas and use functions.
The document provides information on formulas and functions in Excel. It discusses entering formulas using cell references and arithmetic operators. Formulas typically use relative references. Functions are named operations that return values, like SUM to add a range of cells. Common functions include SUM, AVERAGE, COUNT, MIN, and MAX. The document demonstrates using the AutoSum button, absolute references, VLOOKUP and HLOOKUP functions, logical IF functions, and date functions. It also covers conditional formatting.
This document provides an overview of a training course on intermediate Microsoft Excel skills. It covers topics such as logical functions, financial functions, lookup functions, sorting and filtering data, working with dates and times, data validation, formula auditing, the Solver tool, data consolidation, pivot tables, and preparing data for analysis. The training is designed to help participants learn how to enter and navigate data, create formulas to solve problems, use charts and graphs, and reference data using relative and absolute cell references. The first lecture demonstrates how to refresh Excel skills, design better spreadsheets, and improve readability through cell styles and data validation.
The document provides an introduction to Excel basics, including:
- What a spreadsheet is and its key components like rows, columns, and cells.
- How to enter and format data, insert and delete rows and columns, and move or copy data within a spreadsheet.
- How to use formulas with mathematical and logical operators, functions, and cell references.
- Features for analyzing data like sorting, filtering, and creating charts and graphs.
Excel Chapter 2 - Inserting Formulas in a Worksheetdpd
This document provides an overview of inserting formulas in Microsoft Excel 2007. It discusses writing formulas using mathematical operators and functions such as AVERAGE, MAX, MIN, COUNT, PMT, FV, DATE, NOW, and IF. It describes how to type formulas, copy formulas, use cell references, and display formulas. Functions allow calculations to be performed on values and include statistical, financial, and logical functions. The document also covers absolute and mixed cell references.
Intermediate ms excel for business elective course for dlsu-d hsMarkFreudBolima
This document provides an overview of an intermediate Microsoft Excel course for business. The course objectives are to provide an understanding of intermediate Excel components, methods for applying functions and formulas to data, and effective ways to display and manage data. Key topics covered in the course include filling data automatically, referencing cells in formulas, using logical functions and conditional formatting, sorting and filtering data, and creating and formatting charts.
Top 20 microsoft excel formulas you must knowAlexHenderson59
The document provides a list of the top 20 Microsoft Excel formulas that users must know to become more proficient with Excel. It begins by explaining that a formula in Excel calculates values within a range of cells or a single cell. The list then provides examples of commonly used formulas like SUM, MAX, MIN, COUNT, IF, and CONCATENATE. It moves on to more advanced formulas that combine functions like INDEX MATCH, CHOOSE, IF with AND/OR, and concludes with formulas like CONCATENATE that can make worksheets more dynamic. The overall document serves as a useful guide to important Excel formulas for both basic and advanced users.
This document discusses formulas, functions, and ranges in Excel. It explains that formulas perform calculations using values, functions, cell references, operators, and constants. Common functions are SUM, AVERAGE, and IF. Ranges can be used to refer to contiguous or non-contiguous groups of cells. Formulas use cell references, arithmetic operators like + and -, and comparison operators like = and <. Parentheses can be used to specify calculation order.
This document provides an overview of formulas and functions in Excel 2003, including MAX, MIN, AVG, IF, and nested IF functions. It explains terminology like formulas, functions, arguments, cell references, and ranges. Hands-on exercises walk through using the AVERAGE, MAX, MIN, IF, and SUMIF functions to calculate statistics and values based on conditional criteria for datasets in Excel worksheets. The document encourages visiting another site for more educational documents and technological information.
This document provides an overview of formulas and functions in MS Excel. It explains that formulas perform calculations using values, cell references, operators, and functions. Common operators include arithmetic, comparison, and reference operators. The document lists many important functions organized by category and provides examples of commonly used functions like SUM, AVERAGE, IF, and VLOOKUP. It describes proper syntax for functions and how functions can be nested within other functions.
Excel allows users to enter formulas and functions to calculate values. Formulas use mathematical operators and cell references to perform calculations. Functions, like SUM and AVERAGE, are predefined formulas that perform common calculations. Excel provides features like AutoSum, AutoComplete, and error checking to help users easily enter formulas and functions.
Excel basics for everyday use-the more advanced stuffKevin McLogan
This document provides a summary of an Excel basics course. The course objectives are to understand references, ranges, IF functions, lookups, times, filters, and validation. It discusses relative and absolute references, naming ranges, formulas, IF statements, VLOOKUP, dates, times, tracing errors, and validation. The goal is for students to build skills to create spreadsheets that impress others and strike fear into enemies.
This document summarizes an Excel training session that covered various Excel topics including basics, formulas, charts, printing, and mail merges. The training was led by three organizers and included an overview of Excel components and functions, hands-on exercises to create a shopping list and chart, and demonstrations of how to print worksheets, insert formulas, and use mail merges to generate reports and send emails. Additional topics discussed how Pearl and Microsoft Access relate to accessing and analyzing organizational data stored in the database.
Excel allows users to create and edit spreadsheets. It contains tools for entering and formatting data, performing calculations with formulas and functions, and creating charts and graphs. Common Excel functions include SUM, AVERAGE, COUNT, and financial calculations. Users can format cells, insert and delete rows/columns, move or copy data, and fill cells automatically with a series.
Week 5_Lesson_Working with formulas and Functions.pptxMelvyEspanol1
This document provides an overview of using formulas and functions in Microsoft Excel. It covers topics such as copying formulas with relative, absolute and mixed references; understanding function syntax; inserting functions using the Insert Function dialog box or by typing them directly; using AutoFill to copy formulas across a range; using logical functions like IF; and financial functions like PMT to calculate loan payments. The objectives are to learn skills for working efficiently with formulas and functions in Excel.
1. This document provides an introduction to using formulas and functions in Excel, including the basics of adding, subtracting, multiplying, and dividing in Excel without functions, as well as an overview of more advanced functions like SUM, TODAY, COUNT, and AVERAGE.
2. Key functions introduced include SUM, which totals the values in a range of cells; TODAY, which returns the current date; COUNT, which counts the number of cells in a range that contain numbers; and AVERAGE, which calculates the average of the values in a range.
3. The document explains best practices for using formulas with cell references rather than hard-coded values to allow for easy updating, and demonstrates how to enter functions
This document provides an overview of Excel formulas and functions including MAX, MIN, AVG, IF, and nested IF functions. It includes examples and step-by-step instructions for using these functions to calculate statistics and conditional values. Hands-on exercises guide the user through entering formulas to find averages, maximums, minimums, assign letter grades, and conditionally sum values. The document also introduces more advanced statistical functions and the Analysis ToolPak add-in.
This document provides an introduction to Microsoft Excel. It defines key Excel concepts like spreadsheets, worksheets, cells, formulas and functions. It explains how to enter and format data, write formulas, and create basic charts. It also covers best practices for building a simple spreadsheet, including inserting and deleting rows/columns, formatting cells, and naming worksheets. The objectives are to define spreadsheets, introduce basic Excel features, and review important vocabulary.
Excel basics for everyday use part threeKevin McLogan
This document provides an overview of several Excel functions and concepts covered in an Excel basics course, including references, naming cell ranges, IF functions, VLOOKUPs, date and time calculations, and filters. The course objectives are to understand different cell references, name ranges, use IF and lookup functions, calculate times, and filter data quickly.
Here are the steps to calculate the total sales of a fast food store using MS Excel:
1. Create a new Excel worksheet and enter the sales data for each item and month.
2. Use the SUM function to add the sales for each item across the months. For example, =SUM(B2:D2) to add the sales for Hamburgers.
3. Copy the SUM formula down to calculate the totals for each item.
4. Use another SUM function to add the item totals together and calculate the grand total sales for the third quarter.
By setting up the worksheet with formulas, you can easily calculate totals and summaries from the raw sales data. The SUM function is very useful for adding
The document provides an introduction to Excel basics, including:
- What a spreadsheet is and its key components like rows, columns, and cells.
- How to enter and format data, insert and delete rows and columns, and move or copy data within a spreadsheet.
- How to use formulas with mathematical and logical operators, functions, and cell references.
- Features for analyzing data like sorting, filtering, and creating charts and graphs.
Excel Chapter 2 - Inserting Formulas in a Worksheetdpd
This document provides an overview of inserting formulas in Microsoft Excel 2007. It discusses writing formulas using mathematical operators and functions such as AVERAGE, MAX, MIN, COUNT, PMT, FV, DATE, NOW, and IF. It describes how to type formulas, copy formulas, use cell references, and display formulas. Functions allow calculations to be performed on values and include statistical, financial, and logical functions. The document also covers absolute and mixed cell references.
Intermediate ms excel for business elective course for dlsu-d hsMarkFreudBolima
This document provides an overview of an intermediate Microsoft Excel course for business. The course objectives are to provide an understanding of intermediate Excel components, methods for applying functions and formulas to data, and effective ways to display and manage data. Key topics covered in the course include filling data automatically, referencing cells in formulas, using logical functions and conditional formatting, sorting and filtering data, and creating and formatting charts.
Top 20 microsoft excel formulas you must knowAlexHenderson59
The document provides a list of the top 20 Microsoft Excel formulas that users must know to become more proficient with Excel. It begins by explaining that a formula in Excel calculates values within a range of cells or a single cell. The list then provides examples of commonly used formulas like SUM, MAX, MIN, COUNT, IF, and CONCATENATE. It moves on to more advanced formulas that combine functions like INDEX MATCH, CHOOSE, IF with AND/OR, and concludes with formulas like CONCATENATE that can make worksheets more dynamic. The overall document serves as a useful guide to important Excel formulas for both basic and advanced users.
This document discusses formulas, functions, and ranges in Excel. It explains that formulas perform calculations using values, functions, cell references, operators, and constants. Common functions are SUM, AVERAGE, and IF. Ranges can be used to refer to contiguous or non-contiguous groups of cells. Formulas use cell references, arithmetic operators like + and -, and comparison operators like = and <. Parentheses can be used to specify calculation order.
This document provides an overview of formulas and functions in Excel 2003, including MAX, MIN, AVG, IF, and nested IF functions. It explains terminology like formulas, functions, arguments, cell references, and ranges. Hands-on exercises walk through using the AVERAGE, MAX, MIN, IF, and SUMIF functions to calculate statistics and values based on conditional criteria for datasets in Excel worksheets. The document encourages visiting another site for more educational documents and technological information.
This document provides an overview of formulas and functions in MS Excel. It explains that formulas perform calculations using values, cell references, operators, and functions. Common operators include arithmetic, comparison, and reference operators. The document lists many important functions organized by category and provides examples of commonly used functions like SUM, AVERAGE, IF, and VLOOKUP. It describes proper syntax for functions and how functions can be nested within other functions.
Excel allows users to enter formulas and functions to calculate values. Formulas use mathematical operators and cell references to perform calculations. Functions, like SUM and AVERAGE, are predefined formulas that perform common calculations. Excel provides features like AutoSum, AutoComplete, and error checking to help users easily enter formulas and functions.
Excel basics for everyday use-the more advanced stuffKevin McLogan
This document provides a summary of an Excel basics course. The course objectives are to understand references, ranges, IF functions, lookups, times, filters, and validation. It discusses relative and absolute references, naming ranges, formulas, IF statements, VLOOKUP, dates, times, tracing errors, and validation. The goal is for students to build skills to create spreadsheets that impress others and strike fear into enemies.
This document summarizes an Excel training session that covered various Excel topics including basics, formulas, charts, printing, and mail merges. The training was led by three organizers and included an overview of Excel components and functions, hands-on exercises to create a shopping list and chart, and demonstrations of how to print worksheets, insert formulas, and use mail merges to generate reports and send emails. Additional topics discussed how Pearl and Microsoft Access relate to accessing and analyzing organizational data stored in the database.
Excel allows users to create and edit spreadsheets. It contains tools for entering and formatting data, performing calculations with formulas and functions, and creating charts and graphs. Common Excel functions include SUM, AVERAGE, COUNT, and financial calculations. Users can format cells, insert and delete rows/columns, move or copy data, and fill cells automatically with a series.
Week 5_Lesson_Working with formulas and Functions.pptxMelvyEspanol1
This document provides an overview of using formulas and functions in Microsoft Excel. It covers topics such as copying formulas with relative, absolute and mixed references; understanding function syntax; inserting functions using the Insert Function dialog box or by typing them directly; using AutoFill to copy formulas across a range; using logical functions like IF; and financial functions like PMT to calculate loan payments. The objectives are to learn skills for working efficiently with formulas and functions in Excel.
1. This document provides an introduction to using formulas and functions in Excel, including the basics of adding, subtracting, multiplying, and dividing in Excel without functions, as well as an overview of more advanced functions like SUM, TODAY, COUNT, and AVERAGE.
2. Key functions introduced include SUM, which totals the values in a range of cells; TODAY, which returns the current date; COUNT, which counts the number of cells in a range that contain numbers; and AVERAGE, which calculates the average of the values in a range.
3. The document explains best practices for using formulas with cell references rather than hard-coded values to allow for easy updating, and demonstrates how to enter functions
This document provides an overview of Excel formulas and functions including MAX, MIN, AVG, IF, and nested IF functions. It includes examples and step-by-step instructions for using these functions to calculate statistics and conditional values. Hands-on exercises guide the user through entering formulas to find averages, maximums, minimums, assign letter grades, and conditionally sum values. The document also introduces more advanced statistical functions and the Analysis ToolPak add-in.
This document provides an introduction to Microsoft Excel. It defines key Excel concepts like spreadsheets, worksheets, cells, formulas and functions. It explains how to enter and format data, write formulas, and create basic charts. It also covers best practices for building a simple spreadsheet, including inserting and deleting rows/columns, formatting cells, and naming worksheets. The objectives are to define spreadsheets, introduce basic Excel features, and review important vocabulary.
Excel basics for everyday use part threeKevin McLogan
This document provides an overview of several Excel functions and concepts covered in an Excel basics course, including references, naming cell ranges, IF functions, VLOOKUPs, date and time calculations, and filters. The course objectives are to understand different cell references, name ranges, use IF and lookup functions, calculate times, and filter data quickly.
Here are the steps to calculate the total sales of a fast food store using MS Excel:
1. Create a new Excel worksheet and enter the sales data for each item and month.
2. Use the SUM function to add the sales for each item across the months. For example, =SUM(B2:D2) to add the sales for Hamburgers.
3. Copy the SUM formula down to calculate the totals for each item.
4. Use another SUM function to add the item totals together and calculate the grand total sales for the third quarter.
By setting up the worksheet with formulas, you can easily calculate totals and summaries from the raw sales data. The SUM function is very useful for adding
(𝐓𝐋𝐄 𝟏𝟎𝟎) (𝐋𝐞𝐬𝐬𝐨𝐧 3)-𝐏𝐫𝐞𝐥𝐢𝐦𝐬
Lesson Outcomes:
- students will be able to identify and name various types of ornamental plants commonly used in landscaping and decoration, classifying them based on their characteristics such as foliage, flowering, and growth habits. They will understand the ecological, aesthetic, and economic benefits of ornamental plants, including their roles in improving air quality, providing habitats for wildlife, and enhancing the visual appeal of environments. Additionally, students will demonstrate knowledge of the basic requirements for growing ornamental plants, ensuring they can effectively cultivate and maintain these plants in various settings.
Artificial Intelligence (AI) has revolutionized the creation of images and videos, enabling the generation of highly realistic and imaginative visual content. Utilizing advanced techniques like Generative Adversarial Networks (GANs) and neural style transfer, AI can transform simple sketches into detailed artwork or blend various styles into unique visual masterpieces. GANs, in particular, function by pitting two neural networks against each other, resulting in the production of remarkably lifelike images. AI's ability to analyze and learn from vast datasets allows it to create visuals that not only mimic human creativity but also push the boundaries of artistic expression, making it a powerful tool in digital media and entertainment industries.
How to Create a Stage or a Pipeline in Odoo 17 CRMCeline George
Using CRM module, we can manage and keep track of all new leads and opportunities in one location. It helps to manage your sales pipeline with customizable stages. In this slide let’s discuss how to create a stage or pipeline inside the CRM module in odoo 17.
The Science of Learning: implications for modern teachingDerek Wenmoth
Keynote presentation to the Educational Leaders hui Kōkiritia Marautanga held in Auckland on 26 June 2024. Provides a high level overview of the history and development of the science of learning, and implications for the design of learning in our modern schools and classrooms.
Decolonizing Universal Design for LearningFrederic Fovet
UDL has gained in popularity over the last decade both in the K-12 and the post-secondary sectors. The usefulness of UDL to create inclusive learning experiences for the full array of diverse learners has been well documented in the literature, and there is now increasing scholarship examining the process of integrating UDL strategically across organisations. One concern, however, remains under-reported and under-researched. Much of the scholarship on UDL ironically remains while and Eurocentric. Even if UDL, as a discourse, considers the decolonization of the curriculum, it is abundantly clear that the research and advocacy related to UDL originates almost exclusively from the Global North and from a Euro-Caucasian authorship. It is argued that it is high time for the way UDL has been monopolized by Global North scholars and practitioners to be challenged. Voices discussing and framing UDL, from the Global South and Indigenous communities, must be amplified and showcased in order to rectify this glaring imbalance and contradiction.
This session represents an opportunity for the author to reflect on a volume he has just finished editing entitled Decolonizing UDL and to highlight and share insights into the key innovations, promising practices, and calls for change, originating from the Global South and Indigenous Communities, that have woven the canvas of this book. The session seeks to create a space for critical dialogue, for the challenging of existing power dynamics within the UDL scholarship, and for the emergence of transformative voices from underrepresented communities. The workshop will use the UDL principles scrupulously to engage participants in diverse ways (challenging single story approaches to the narrative that surrounds UDL implementation) , as well as offer multiple means of action and expression for them to gain ownership over the key themes and concerns of the session (by encouraging a broad range of interventions, contributions, and stances).
2. What is it?: Spreadsheets Basics
• Spreadsheet is a computerized ledger
• Divided into Rows and Columns
• Excel is a “spreadsheet” which holds different kinds
of information
• It performs calculations with mathematical and
statistical functions
• It presents your information in a variety of ways, with
visually interesting charts and graphs
• Constants - entries that do not change
• Formulas - combination of constants and functions
• Spreadsheet is generic term; Worksheet is an Excel
term
• Workbook contains one or more worksheets
6. Copying formulas does not always work
This formula calculates first-quarter sales as a percentage of total year sales.
Not surprisingly, it divides the number in cell B4 ($1,000) by the number in cell F4 ($4,100).
However, when you pull the extension handle to copy the formula for Quarters 2,3, and 4,
you don’t get the results you want. Press the Page Down key several times to see what
happens.
#DIV/0! means Excel is trying to tell you that you have asked it to divide by 0 -- an operation
for which there is no answer. Why is this happening?
7. Formula references
To understand why this may be so, consider what the above formula is really saying.
To Excel, B4/F4 actually translates as “when I’m sitting in cell B5, divide the number that is
one row above me by the number that is four columns to my right and one row up.”
In other words, the formula as written tells Excel to make a calculation relative to the cursor’s
current position. Remember: the cursor always tells to Excel its current location (note B5 in
the name box above).
What you need to do for the formula to copy correctly is to tell Excel always to use the
$4,100 (i.e., the contents of cell F4) -- no matter where you copy the formula.
To do this, you need to make the reference to cell F4 an absolute reference rather than a
relative reference. Use Page Down to see how to do this.
8. Use the F4 key for absolute references
The screen above shows the formula corrected so that it will give the desired results after it
is copied.
To convert a relative reference to an absolute reference while building a formula, press the
F4 key immediately after clicking on the desired location with the cursor (in this case, the
immediately after selecting the cell that has $4,100 in it).
The formula changes to show a $ in front of both the column and the row for that location.
You will notice that if you continue to press the F4 key, the command toggles to set the
column, then the row, then both as absolute references.
Trivia: Excel uses the $ to indicate absolute references because in the early days of spreadsheets PC keyboards had
so few keys that software designers had to find multiple uses for each symbol. The company that first did this now
out of business, but the $ stuck.
9. Use the F4 key for absolute references
This screen shows the result after you copy a formula that is correctly referenced.
Use the Page Down key to walk through the steps.
When you’re done, use Page Down to see a sequence that shows setting the absolute
reference.
10. Setting an absolute reference
The F4 key has been pressed to right here to lock this location as an absolute reference.
This time, all the copied formulas refer correctly to the $4,100 because that location has
been set in the original formula as an absolute reference.
You can check the results by looking at the copied formulas. They all refer to $F$4.
11. Using Functions in Excel
• Use spreadsheets in decision making; use Goal Seek
and Scenario Manager to evaluate multiple conditions
• Use financial functions (PMT, etc.)
• Use fill handle and AutoFill capability
• Use pointing to create a formula
• Statistical Functions — MAX, MIN, AVERAGE, COUNT
• Use functions over arithmetic expressions
• Decision making functions (IF and VLOOKUP (vertical
lookup))
13. SUM(number1, number 2,…)
• Example
=SUM(3, 2) equals 5
• If cells A2:E2 contain 5, 15, 30, 40, and 50:
=SUM(A2:C2) equals 50 =SUM(B2:E2, 15) equals
150
14. AVERAGE(number 1, number 2,…)
• Examples
If A1:A5 is named Scores and contains the numbers
10, 7, 9, 27, and 2, then:
=AVERAGE(A1:A5) equals 11
=AVERAGE(Scores) equals 11
=SUM(A1:A5)/COUNT(A1:A5) equals 11
15. SIN(number)
• IMPORTANT NOTE:
– Angle (number) must be provided in radians If your
argument is in degrees, multiply it by PI()/180 to
convert it to radians.
=SIN(PI()) equals 1.22E-16, which is approx. 0
=SIN(PI()/2) equals 1
=SIN(30*PI()/180) equals 0.5, the sine of 30 degrees
16. COUNT
• COUNT counts the number of cells that contain
numbers & numbers within the list of arguments.
• Value 1, 2,…, are 1 to 30 arguments that can contain
or refer to a variety of different types of data, but only
numbers are counted.
• Ex., If cells A1:A17 contain some data, then
=COUNT(A1:A17) equals 17
=COUNT(A6:A17) equals 12
17. COUNTIF(range,criteria)
Counts the number of cells within a range that meet the
given criteria.
Suppose A3:A6 contain "apples", "oranges", "peaches",
"apples", respectively:
COUNTIF(A3:A6,"apples") equals 2
Suppose B3:B6 contain 32, 54, 75, 86, respectively:
COUNTIF(B3:B6,">55") equals 2
23. Conditional Functions
• Conditional functions allow the software to perform
conditional tests and evaluate a condition in your
worksheet. Depending on whether the condition is
true or false, different values will be returned to the
cells.
• =IF is the most important conditional function
24. If
=IF(condition, action if true, action if false)
This tests the “condition” to determine if specific results
or cell contents are true or false.
If the result of the test is true, the “action if true” is
executed. If the result is false, the “action if false”
portion contains another set of instructions to
execute.
The instructions to be executed can return cell contents
that are labels as well as values.
25. Logical Operators
• To perform conditional tests, logical operators are
required.
= Equal
< Less than
> Greater than
<= Less than or Equal to
>= Greater than or Equal to
<> Not Equal
26. Logical Functions
And(logical1, logical2) Returns true if each
condition is true
Or(logical1, logical2) Returns true if either
condition is true
Not(logical) Returns true if the
condition is false
True() Always returns true
False() Always returns false
27. Examples
=IF(A5>20, B5, 0) means that if the value in A5 is greater than
20, use the value in B5. Otherwise assign the number 0.
=IF(AND(B11<>0,G11=1),10,0) means that if the value in B11
is not equal to 0 and the value in G11 is equal to 1, assign
the number 10. Otherwise, assign the number 0.
=IF(OR(E13=“Profit”,F15>G15),”Surplus”,”Deficit”) means
that if either E13 contains the word “Profit” or the contents
of F15 are greater than or equal to the contents of G15,
assign the label “Surplus”. Otherwise, assign the label
“Deficit”.
28. VLOOKUP Function
• Searches for a value in the leftmost column of a table,
and then returns a value in the same row from a
column you specify in the table. Use VLOOKUP
instead of HLOOKUP when your comparison values
are located in a column to the left of the data you want
to find.
Syntax:
=VLOOKUP(lookup_value,table_array,
col_index_num,range_lookup)
– If range_lookup is TRUE, the values in the first column of
table_array must be placed in ascending order: ..., -2, -1, 0, 1,
2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give
the correct value. If range_lookup is FALSE, table_array does
not need to be sorted.
29. VLOOKUP Function (cont’d)
• Example:
On the preceding worksheet, where the range A4:C12 is named
Range:
– VLOOKUP(1,Range,2) equals 2.17
– VLOOKUP(1,Range,3,TRUE) equals 100
– VLOOKUP(.746,Range,3,FALSE) equals 200
– VLOOKUP(0.1,Range,2,TRUE) equals #N/A, because 0.1 is less than the
smallest value in column A
– VLOOKUP(2,Range,2,TRUE) equals 1.71
30. Functions within functions
• You can use functions within functions. Consider the
expression =ROUND(AVERAGE(A1:A100),1).
– This expression would first compute the average of all the
values from cell A1 through A100 and then round that result
to 1 digit to the right of the decimal point
31. Open the Insert Function dialog box
• To get help from Excel to insert a function, first click
the cell in which you wish to insert the function.
• Click the Insert Function button. This action will open
the Insert Function dialog box.
• If you do not see the Insert Function button, you may
need to select the appropriate toolbar or add the
button to an existing toolbar.
32. Examine the Insert Function
dialog box
This dialog box appears when you click the Insert Function
button. It can assist you in defining your function.
33. Circular reference
Situation when some parameter in the formula refers to
the formula itself
For example, in cell C5 the following formula is entered:
VLOOKUP(C5, F2:G15, 2, TRUE)
The address C5 refers to the formula itself.
In this case it is a wrong way of writing the formula.
In some cases circular references may be used
for creating recursive (recurrent) functions.
35. Excel's financial functions
• Financial functions are very useful to calculate information about
loans.
• Common functions are FV, IPMT, PMT, PPMT and PV.
• All these financial functions will use similar arguments that differ
based upon which function you are using.
– Think of the arguments as members of an equation
– The arguments represent the values of the equation that are known
and the function provides the solution for a single variable, or
unknown, value
36. Use the financial functions
• The FV function calculates the future value of an investment
based on periodic, constant payments and a constant interest
rate per period.
• The IPMT function provides the interest payment portion of the
overall periodic loan payment.
• The PMT function calculates the entire periodic payment of the
loan.
• The PPMT function calculates just the principal payment portion
of the overall periodic payment.
• The PV function calculates the present value of an investment.
37. Use the financial functions (cont’d)
• NPER Determines the number of payments needed for an
investment to grow or pay back a loan
• RATE Determines the effective interest rate
38. Use the Insert Function dialog box to enter
function arguments
This figure depicts how you would enter argument values for
the PMT function using the Insert Function dialog box.
39. Annuity - Dictionary Definition
• An annual allowance or income; also, the right to
receive such an allowance or the duty of paying it.
(First definition in Britannica World Language edition of Funk & Wagnalls
Standard Dictionary, 1966)
• We allow the payments to be more frequent than yearly
40. Loans and annuities
• A typical loan is an annuity: Why? The borrower
promises to pay a fixed amount every period.
• When we retire we want to set up a pension. We give
a bank some money. In return the bank promises to
pay us a fixed payment every month for a given
number of years. We can treat this as a loan:
– We loaned the bank the money.
– The bank promises to pay us back with a regular payment.
41. Simple and Compound Interest
• Simple interest: Interest is not paid on interest
• Compound interest: Interest is paid on interest
• Compounding per year: Number of times interest is
paid or charged each year
43. Example of loan
You borrow $2000 at 12% annual interest compounded monthly.
What is your payment if you pay off the loan in 6 months?
Interest rate (APR) 12% per year
Years 0.5
Principal $2,000.00
Payments $345.10
45. Standard arguments
• rate: Interest rate (in decimal) per period
• nper: Number of periods
• pmt: Regular payment
46. More standard arguments
• pv: Present value: The amount the
series of future payments is worth
now. The beginning value.
• fv: Future value: The amount the
series of future payments will be
worth in the future. The final value.
• type 0 = payment at end of period (default)
1 = payment at beginning
47. PMT( ) Payments
• Returns the periodic payment for an annuity or loan
• PMT(rate, nper, pv, fv, type)
• The first 3 (red) arguments are required
• Example : 12% interest compounded monthly, 3 years,
borrow $6021.50
• Monthly payment
= -PMT(.12/12, 3*12, 6021.50) =200.00
48. PMT( ) Example
• Ms JustRetired has $200,000 to invest at 10% annual
interest. Her goal is to have $100,000 left after 5
years. If she makes equal withdrawals each year for 5
years, how much can she withdraw each year?
• PMT(rate, nper, pv, fv, type)
= PMT(0.10, 5, -200000, +100000)
= $36,380
49. PV( ) Present Value
• Returns the present value of an investment. The
present value is the total amount that a series of
future payments is worth now. That is, it is the
beginning value of the investment or loan.
• PV(rate, nper, pmt, fv, type)
50. PV( ) Example
• A person promises to pay you $200 per month for 3
years. If you assume 12% interest compounded
monthly, what is this annuity worth today?
• How much can you borrow at 12% annual interest
compounded monthly and repay in 3 years paying
$200 per month?
• = -PV(0.12/12, 3*12, 200) = 6021.50
51. FV( ) Future Value
• Returns the future (final) value of an investment. The
future value is the total amount including interest that
series of payments will be worth.
• How much money will there be in your account if you
make regular payments for a period of time?
• FV(rate, nper, pmt, pv, type)
52. FV( ) Examples
• You will make $200 a month payments into a 12%
annual interest payable monthly account. How much
will you have after 3 years?
• = -FV(0.12/12, 3*12, 200) = 8615.38
• You will put $1000 in your account that pays 5%
annually compounded monthly. You will add $100 to
the account every month. How much will you have
after 10 years?
• = -FV(0.05/12, 10*12, 100, 1000) = 17175.24
53. NPER( ) Number payments needed for an investment
• Returns the number of periods for an investment
based on periodic, constant payments and a constant
interest rate
• NPER(rate, pmt, pv, fv, type)
Example:
• Each month you put $200 into bank account.
Assuming a 12% annual interest rate payable monthly,
how many years will you need to save before you
have $8615.38 in the account?
• = NPER( 0.01, 200, 0, -8615.38)/12 = 3
54. RATE( ) Effective interest rate
• Returns the interest rate per period of an annuity
• Gives the effective interest rate given the number of
periods, the periodic payment, final value and initial
value
• RATE(nper, pmt, pv, fv, type)
Example:
• You bought $1,000 of shares in a mutual fund initially and then
$100 more each month. After 10 years, your shares are worth
$17,175.24. What was the effective interest rate? (Assume the
interest is compounded monthly and paid at the end of the
month.)
• =RATE(12*10, 100, 1000, -17175.24, 0)*12
56. Date Functions
=Date coverts a date into a date number. Excel can
represent any given date as a serial number equal to the
number of days from Dec. 31, 1899 to the date in question.
=DATE(year number, month number, day number)
Enter the date into a cell (F5) with the =Date function. Then
enter the Report date with the =Date function into another
cell (L6). The difference between these dates (in number
of days) can be calculated with the formula =L6-F5.
57. Commonly used date functions
Since dates are stored as integers, you can subtract one date from another to see
how many days there are separating the two dates. The figure below provides
additional details about the common date functions in Excel.
58. The TODAY and NOW functions
• The TODAY and NOW functions always display the
current date and time.
• You will not normally see the time portion unless you
have formatted the cell to display it.
• If you use the TODAY or NOW function in a cell, the
date in the cell is updated to reflect the current date
and time of your computer each time you open the
workbook.
66. Types of Numeric Formats
General
Number
Currency
Accounting
Date
Time
Percentage
Fraction
Scientific
Text
Special
Custom
67. Comments
• Right click on the cell & select Insert comment.
• You may edit or delete the comment by right clicking
on the cell & selecting your choice.
Y 790,343 695,034 263,448
Y 1,934,349 1,793,090 483,587
Y 2,103,049 2,001,304 420,610
N 1,785,323 1,593,032 357,065
Tanya Goette:
This store looks
really great!