MS-Excel formulas and functions

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to Lesson

Podcast

Listen to an AI-generated conversation about this lesson
Download our mobile app to listen on the go
Get App

Questions and Answers

In Excel, what are formulas used for?

  • Formatting cell appearance.
  • Performing calculations. (correct)
  • Displaying static text.
  • Creating charts.

What is the first character you must type to start a formula in Excel?

  • #
  • &
  • = (correct)
  • @

What is a cell reference?

  • The set of formatting options applied to a cell.
  • A function that returns a cell's value.
  • A predefined formula.
  • The address of a cell in a worksheet. (correct)

Which of the following is an example of a cell reference?

<p>A1 (A)</p>
Signup and view all the answers

What does a relative cell reference do when copied across multiple cells?

<p>Adjusts based on the new row and column. (D)</p>
Signup and view all the answers

What symbol is used to make a cell reference absolute?

<p>$ (C)</p>
Signup and view all the answers

Which of the following is an example of an absolute cell reference?

<p>$A$1 (B)</p>
Signup and view all the answers

In mixed cell referencing, which part of the cell reference remains constant?

<p>Either the row or column. (A)</p>
Signup and view all the answers

What does the F4 key do when editing a cell reference in a formula?

<p>Cycles through relative, absolute, and mixed references. (C)</p>
Signup and view all the answers

What is the purpose of function syntax in Excel?

<p>To define how a function should be written. (D)</p>
Signup and view all the answers

In the function =SUM(A1:A10), what does A1:A10 represent?

<p>A range of cells. (A)</p>
Signup and view all the answers

Which Excel function calculates the average of a range of numbers?

<p>AVERAGE (A)</p>
Signup and view all the answers

Which function counts the number of cells that contain numbers?

<p>COUNT (D)</p>
Signup and view all the answers

What is the difference between the COUNT function and the COUNTA function?

<p><code>COUNT</code> counts cells containing numbers, while <code>COUNTA</code> counts non-empty cells. (A)</p>
Signup and view all the answers

Which Excel function returns the largest value in a set of numbers?

<p>MAX (A)</p>
Signup and view all the answers

What does the MEDIAN function determine?

<p>The middle value. (C)</p>
Signup and view all the answers

Which Excel function finds the smallest number in a range of cells?

<p>MIN (A)</p>
Signup and view all the answers

Which function returns a random number between 0 and 1?

<p>RAND (D)</p>
Signup and view all the answers

What does the ROUND function do?

<p>Rounds a number to a specified number of digits. (A)</p>
Signup and view all the answers

On which tab can you find the Formulas tab in Excel?

<p>Formulas (A)</p>
Signup and view all the answers

What is the purpose of the Insert Function button?

<p>To find and insert functions into a formula. (D)</p>
Signup and view all the answers

Which tab in Excel contains the Fill button?

<p>Home (C)</p>
Signup and view all the answers

What is AutoFill used for?

<p>Automatically completing number series or copying formulas. (C)</p>
Signup and view all the answers

By default, what does AutoFill copy when dragging the fill handle?

<p>Both formulas and formats. (C)</p>
Signup and view all the answers

If you only want to copy the formatting using AutoFill, which option should you select?

<p>Fill Formatting Only (C)</p>
Signup and view all the answers

What is the purpose of the IF function in Excel?

<p>To perform a logical comparison. (D)</p>
Signup and view all the answers

What type of value is checked with the IF function?

<p>Logical (D)</p>
Signup and view all the answers

In an IF function, what is the purpose of the 'value_if_true' argument?

<p>It returns a value if the condition is true. (A)</p>
Signup and view all the answers

Which symbol means 'equal to' in an Excel logical test?

<p>= (D)</p>
Signup and view all the answers

In Excel, what does the '<>' operator mean?

<p>Not equal to. (B)</p>
Signup and view all the answers

Which function displays the current date?

<p>TODAY() (C)</p>
Signup and view all the answers

Which function displays the current date and time?

<p>NOW() (C)</p>
Signup and view all the answers

Which function is used to calculate loan payments?

<p>PMT (B)</p>
Signup and view all the answers

In the PMT function, what does rate refer to?

<p>The interest rate per period. (D)</p>
Signup and view all the answers

In the PMT function, what does nper stand for?

<p>Number of periods. (A)</p>
Signup and view all the answers

Which of the following describes a relative cell reference accurately?

<p>It adjusts automatically when copied to another cell, based on the relative position. (C)</p>
Signup and view all the answers

When should you use an absolute cell reference in an Excel formula?

<p>When you need to refer to a cell that will always stay the same, regardless of where the formula is copied. (A)</p>
Signup and view all the answers

In the context of mixed cell references, what does $A1 signify?

<p>The column is absolute, and the row is relative (A)</p>
Signup and view all the answers

If a formula in cell C1, =$A1+B$1, is copied to cell D2, what will the formula in D2 be?

<p>=$A2+B$1 (B)</p>
Signup and view all the answers

Which of the following is the correct syntax for using the SUM function to calculate the sum of cells A1, A2, and A3?

<p>SUM(A1, A2, A3) (B)</p>
Signup and view all the answers

How do you find the Formulas tab in Excel?

<p>It is located on the far right of the ribbon in the default Excel configuration. (C)</p>
Signup and view all the answers

Which of the following scenarios is best suited for using the COUNT function?

<p>Counting the number of cells in a range that contain numerical values. (C)</p>
Signup and view all the answers

You need to count the number of cells in a range that are not empty. Which function should you use?

<p>COUNTA (B)</p>
Signup and view all the answers

Which Excel function would be used to determine the middle value in the dataset [1, 5, 2, 8, 3]?

<p>MEDIAN (D)</p>
Signup and view all the answers

A cell contains the formula =ROUND(4.567, 2). What value will be displayed in the cell?

<p>4.57 (B)</p>
Signup and view all the answers

What steps would you take to use Excel's AutoFill feature insert the months up to June starting from January in a column?

<p>Type 'January' in a cell, drag the fill handle down five cells, and Excel will automatically fill in the months. (C)</p>
Signup and view all the answers

You have the formula =A1+B1 in cell C1. Which AutoFill option should you use if you only want to copy the formula without any formatting to the cells below?

<p>Fill Without Formatting (D)</p>
Signup and view all the answers

What is the result of the following IF function: =IF(10>5, "TRUE", "FALSE")?

<p>TRUE (D)</p>
Signup and view all the answers

In an Excel IF function, what is the data type of the 'logical_test' argument?

<p>It must be a boolean value (TRUE or FALSE) (A)</p>
Signup and view all the answers

Which of the following comparison operators in Excel checks if the value in cell A1 is not the same as that in cell B1?

<p>&lt;&gt; (C)</p>
Signup and view all the answers

Which function would dynamically update a particular cell with the current date?

<p>TODAY() (C)</p>
Signup and view all the answers

Which of the following best describes the rate argument in the PMT function?

<p>The interest rate per period. (D)</p>
Signup and view all the answers

What does the Insert Function button do in Excel?

<p>It opens a dialog box that helps you search for and insert functions. (D)</p>
Signup and view all the answers

While using the Insert Function dialog box, which feature helps you understand the purpose of a selected function?

<p>A description appears below the list of functions. (C)</p>
Signup and view all the answers

What does 'syntax' refer to when discussing Excel functions?

<p>The rules specifying how the function should be written. (B)</p>
Signup and view all the answers

In a function like =AVERAGE(B2:B10), what does B2:B10 represent?

<p>The range of cells the function will use to calculate the average. (A)</p>
Signup and view all the answers

Which function category in Excel would you use to analyze loan interest rates?

<p>Financial (D)</p>
Signup and view all the answers

Which function helps you return text values or evaluate text?

<p>Text (C)</p>
Signup and view all the answers

Where is the fill handle located?

<p>Bottom-Right corner of the selected cell (B)</p>
Signup and view all the answers

What is the effect of setting return_type to 2 in the WEEKDAY function?

<p>Sets Monday to <code>1</code> (B)</p>
Signup and view all the answers

What is the effect of pressing the F4 key multiple times when editing a cell reference?

<p>It cycles the reference from relative to absolute to mixed and then back to relative. (A)</p>
Signup and view all the answers

What is the purpose of the optional arguments [value_if_false] in the IF function?

<p>It is the value that is returned if the logical test is false. (D)</p>
Signup and view all the answers

Which function returns only the integer (whole number) part of a number?

<p>INT (C)</p>
Signup and view all the answers

Which Excel feature can automatically fill a series of dates (e.g., days of the week or months) into adjacent cells?

<p>AutoFill (C)</p>
Signup and view all the answers

If payments are made at the end of the period, what should the type argument be set to?

<p>0 (B)</p>
Signup and view all the answers

To retrieve the third smallest value from a range of cells, which function would it be most appropriate to use?

<p>SMALL (C)</p>
Signup and view all the answers

When working with the AutoFill Options button, what is the difference between Copy Cells and Fill Series?

<p><code>Copy Cells</code> duplicates only the contents of the cell; <code>Fill Series</code> can extend a pattern or sequence. (C)</p>
Signup and view all the answers

What does nper refer to in Excel's PMT financial function?

<p>Number of periods. (A)</p>
Signup and view all the answers

How can you search for a function in Excel if you only know a word or two about what you want to accomplish?

<p>Type a description of the function in the 'Search for a function' box in the Insert Function dialog box. (C)</p>
Signup and view all the answers

What is the primary benefit of using structured function categories like 'Financial' or 'Logical' in Excel?

<p>They help you quickly locate the right function for a specific task. (C)</p>
Signup and view all the answers

Which Excel function is most suitable for calculating the total amount to be received at the maturity of an investment?

<p>FV (B)</p>
Signup and view all the answers

What distinguishes manually typing function names in a formula from using the 'Insert Function' dialog box?

<p>The 'Insert Function' dialog box offers guidance and descriptions for arguments and syntax. (D)</p>
Signup and view all the answers

AutoFill can create a patterned text series. Which of the following series could AutoFill generate?

<p>Product A, Product B, Product C (A)</p>
Signup and view all the answers

Which of the following formulas accurately uses the IF function in Excel?

<p>IF(A1&gt;10, B1, C1) (A)</p>
Signup and view all the answers

Flashcards

Relative Reference

A formula that changes its cell references when copied to a new location.

Absolute Reference

A formula that keeps its original cell references when copied to a new location.

Mixed Reference

A formula that includes both relative and absolute references.

Function Syntax

A set of rules that specifies how a function should be written.

Signup and view all the flashcards

Arguments in Excel

Input values or cell references required to execute a function.

Signup and view all the flashcards

AVERAGE Function

Calculates the average of a collection of numbers.

Signup and view all the flashcards

COUNT Function

Counts how many cells in a range contain numbers.

Signup and view all the flashcards

COUNTA Function

Counts how many cells are not empty in a range.

Signup and view all the flashcards

INT Function

Displays the integer portion of a number.

Signup and view all the flashcards

MAX Function

Calculates the maximum value of a collection of numbers.

Signup and view all the flashcards

MEDIAN Function

Calculates the median, or middle, value of a collection of numbers.

Signup and view all the flashcards

MIN Function

Calculates the minimum value of a collection of numbers.

Signup and view all the flashcards

RAND Function

Returns a random number between 0 and 1.

Signup and view all the flashcards

ROUND Function

Rounds a number to a specified number of digits.

Signup and view all the flashcards

SUM Function

Adds a collection of numbers.

Signup and view all the flashcards

AutoFill

Copies content and formats from a cell or range into an adjacent cell or range.

Signup and view all the flashcards

Logical Function

A function that works with values that are either true or false.

Signup and view all the flashcards

Comparison Operator

A symbol that indicates the relationship between two values.

Signup and view all the flashcards

IF Function

A logical function that returns one value if the statement is true and returns a different value if the statement is false.

Signup and view all the flashcards

DATE Function

Creates a date value for the date represented by the year, month, and day arguments

Signup and view all the flashcards

DAY Function

Extracts the day of the month from the date value.

Signup and view all the flashcards

MONTH Function

Extracts the month number from the date value where 1=January, 2=February, and so forth.

Signup and view all the flashcards

YEAR Function

Extracts the year number from the date value.

Signup and view all the flashcards

WEEKDAY Function

Calculates the day of the week from the date value.

Signup and view all the flashcards

NOW() Function

Returns the current date and time.

Signup and view all the flashcards

TODAY() Function

Displays the current date.

Signup and view all the flashcards

FV Function

Returns the future value of an investment.

Signup and view all the flashcards

PMT Function

Calculates the payments required each period on a loan or investment.

Signup and view all the flashcards

IPMT Function

Calculates the amount of a loan payment devoted to paying the loan interest.

Signup and view all the flashcards

PPMT Function

Calculates the amount of a loan payment devoted to paying off the principal of a loan.

Signup and view all the flashcards

PV Function

Calculates the present value of a loan or investment based on periodic, constant payments.

Signup and view all the flashcards

NPER Function

Calculates the number of periods required to pay off a loan or investment.

Signup and view all the flashcards

RATE Function

Calculates the interest rate of a loan or investment based on periodic, constant payments.

Signup and view all the flashcards

Entering Relative References

To enter cell references as they appear in the worksheet.

Signup and view all the flashcards

Entering Absolute References

Enter a $ before the column and row (e.g., $B$2).

Signup and view all the flashcards

Entering Mixed References

Putting $ before row or column makes it mixed e.g., $B2 or B$2

Signup and view all the flashcards

Inserting a Function

Click Formulas tab> choose function category> select function.

Signup and view all the flashcards

Typing a Function

Begins with an equals= sign, then function name and parentheses.

Signup and view all the flashcards

Filling a Series

Copies and extends data series like dates, numbers using patterns.

Signup and view all the flashcards

Study Notes

About MS-Excel

  • MS-Excel is covered in Part Three, Chapter One of the course
  • The objectives are to build formulas containing relative, absolute, and mixed references
  • You should also review function syntax

Formulas

  • Formulas can contain relative, absolute, and mixed references
  • To enter a relative reference, enter the cell reference as it appears. Example: B2 for cell B2
  • To enter an absolute reference, type $ (dollar sign) before the row and column, like $B$2
  • To enter a mixed reference, type $ before the row or column reference. Example: $B2 or B$2
  • Alternatively, select the cell reference, and use the F4 key to cycle the reference from relative to absolute to mixed

Function Syntax

  • Functions must follow a set of rules, or syntax
  • Arguments are the values or references provided to a function, which it uses to perform calculations or operations

Excel Function Categories and Descriptions:

  • Cube: Retrieves data from multidimensional databases involving online analytical processing or OLAP
  • Database: Retrieves and analyzes data stored in databases
  • Date & Time: Analyzes or creates date and time values and time intervals
  • Engineering: Analyzes engineering problems
  • Financial: Has financial applications
  • Information: Returns information about the format, location, or contents of worksheet cells
  • Logical: Returns logical (true-false) values
  • Lookup & Reference: Looks up and returns data matching specified conditions from a range
  • Math & Trig: Has math and trigonometry applications
  • Statistical: Provides statistical analyses of a set of data
  • Text: Returns text values or evaluates text

Functions

  • AVERAGE(number1 [, number2, number3, ...]): Calculates the average of numbers
  • COUNT(value1 [, value2, value3, ...]): Counts cells containing numbers in a range
  • COUNTA(value1, [,value2, value3, ...]: Counts non-empty cells in ranges
  • INT(number): Displays the integer portion of a number
  • MAX(number1 [, number2, number3, ...]): Calculates the maximum value of numbers
  • MEDIAN(number1 [, number2, number3, ...)): Calculates the median value of numbers
  • MIN(number1 [, number2, number3, ...]): Calculates the minimum value of numbers
  • RAND(): Returns a random number between 0 and 1
  • ROUND(number, num_digits): Rounds a number to a specified number of digits
  • SUM(number1 [, number2, number3, ...]): Adds a collection of numbers

Inserting a Function:

  • Click the Formulas tab on the Ribbon, then select the appropriate category in the Function Library group
  • Alternatively, use the Insert Function button in the Function Library group to search for a function
  • Select the function and enter argument values in the Function Arguments dialog box
  • One can also type a function name within a formula, use the Insert function button on the formula bar located by the ribbon, a list of functions that begin to appear

AutoFill

  • AutoFill copies content/formats from a cell/range into an adjacent cell/range
  • Drag the fill handle to copy formulas, releasing the mouse button when done
  • A button is displayed after filling, where one can choose to copy only the formats or formulas to the selected range

Creating a Series with AutoFill:

  • Enter the first few values of the series into a range
  • Select the range and drag the fill handle
  • Or, choose the Fill button in the Editing group on the Home tab

Logical Functions

  • A logical function works with true/false values
  • The IF function returns one value if the statement is true and another if it's false, following the format: IF(logical_test, value_if_true; [value_if_false])
  • Comparison operators indicate relationships between two values

Comparison Operators:

  • = : Equal to

  • : Greater than

  • < : Less than

  • = : Greater than or equal to

  • <= : Less than or equal to
  • <> : Not equal to

Date Functions:

  • DATE(year, month, day): Creates a date value from year, month, and day arguments
  • DAY(date): Extracts the day of the month from a date value
  • MONTH(date): Extracts the month number from a date value (1=January, 2=February, etc.)
  • YEAR(date): Extracts the year number from a date value
  • WEEKDAY(date, [return_type]): Calculates the day of the week from a date
  • NOW(): Displays the current date and time
  • TODAY(): Displays the current date

Financial Functions:

  • FV(rate, nper, pmt, [pv=0] [,type=0]): Returns the future value of an investment
  • PMT(rate, nper, pv, [fv=0] [,type=0]): Calculates the payments for a loan/investment
  • IPMT(rate, per, nper, pv, [fv=0] [,type=0]): Calculates the loan payment allocated to interest
  • PPMT(rate, per, nper, pv, [fv=0] [,type=0]): Calculates the loan payment allocated to principal
  • PV(rate, nper, pmt, [fv=0] [,type=0]): Calculates the present value of a loan/investment
  • NPER(rate, pmt, pv, [fv=0] [,type=0]): Calculates the number of periods to pay off a loan
  • RATE(nper, pmt, pv, [fv=0] [,type=0]): Calculates the interest rate for a loan/investment

To determine a monthly loan payment

  • Know the annual interest rate
  • It is also important to know the payment period
  • The length of the loan in the number of payment periods need to be known
  • The amount being borrowed or invested must also be known

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Quiz Team

Related Documents

More Like This

Use Quizgecko on...
Open
Browser
Browser