## Table of contents for Microsoft Excel functions & formulas / by Bernd Held.

Bibliographic record and links to related information available from the Library of Congress catalog.

Note: Contents data are machine generated based on pre-publication provided by the publisher. Contents may have variations from the printed book or be incomplete or contain other coding. ```Chapter 1 -- Formulas in Excel
Production per hour
Calculate the age of a person in days
Calculate a price reduction
Convert currency
Convert from hours to minutes
Determine fuel consumption
Calculate your ideal and recommended weight
The quick calendar
Design your own to-do list
Increment row numbers
Convert negative values to positive
Calculate taxes
Combine text and numbers
Combine text and date
Combine text and time
A special ranking list
Determine the average output
Stocks - gain and loss
Evaluate profitability
Calculation with percent
Convert miles per hour to km per hour
Convert feet per minute to meters per second
Convert liters to barrels, gallons, quarts, and pints
Convert from Fahrenheit to Celsius
Convert from Celsius to Fahrenheit
Calculation with percentage
Monitor the daily production plan
Calculate number of hours between two dates
Determine the price per pound
Determine how many pieces to put in a box
Calculate project duration
Distribute sales
Calculate your net income
Calculate percentage of price reduction
Doubling every three hours
Calculate the average speed
Chapter 2 -- Logical Functions
Use the AND function to compare two columns
Use the AND function to show sales for a specific period of time
Use the OR function to check cells for text
Use the OR function to check cells for numbers
Use the IF function to compare columns and return a specific result
Use the IF function to check for larger, equivalent, or smaller values
Combine IF with AND to check several conditions
Use the IF function to determine the quarter of a year
Use the IF function to check cells in worksheets and workbooks
Use the IF function to calculate with different tax rates
Use the IF function to calculate the commissions for individual sales
Use the IF function to compare two cells
Use the INT function with the IF function
Use the TYPE function to check for invalid values
Use the IF function combined in one cell more than seven times
Use the IF function to check for whether a date is in the past or future
Use the IF function to create your own timesheet
Chapter 3 -- Text Functions
Use the LEFT and RIGHT functions to separate a text string of numbers
Use the LEFT function to convert invalid numbers to valid numbers
Use the SEARCH function to separate first name from last name
Use the MID function to separate last name from first name
Use the MID function to sum the digits of a number
Use the EXACT function to compare two columns
Use the SUBSTITUTE function to substitute digits
Use the SUBSTITUTE function to substitute parts of a cell
Use the SUBSTITUTE function to convert numbers to words
Use the SUBSTITUTE function to remove word-wrapping in cells
Use the SUBSTITUTE function to combine and separate
Use the REPLACE function to replace and calculate
Use the FIND function to combine text and date
Use the UPPER function to convert text from lowercase to uppercase
Use the LOWER function to convert text from uppercase to lowercase
Use the PROPER function to convert initial characters from lowercase to uppercase
Use the FIXED function to round and to convert numbers to text
Use the TRIM function to delete spaces
Use the TRIM function to convert "text-numbers" to real numbers
Use the CLEAN function to remove all nonprintable characters
Use the REPT function to show data in graphic mode
Use the REPT function to show data in a chart
Use the CHAR function to check your fonts
Use the CHAR function to determine special characters
Use the CODE function to determine the numeric code of a character
Use the DOLLAR function to convert numbers to currency as text
Use the T function to check for valid numbers
Use the TEXT function to combine and format text
Chapter 4 -- Date and Time Functions
Use custom formatting to determine the day of the week
Use the WEEKDAY function to determine the weekend
Use the TODAY function to check for future dates
Use the TEXT function to calculate with the TODAY function
Use the NOW function to show the current time
Use the NOW to calculate time
Use the DATE function to combine columns with date parts
Use the LEFT, MID, and RIGHT functions to extract date parts
Use the TEXT function to extract date parts
Use the DATEVALUE function to calculate with "text dates"
Use the YEAR function to extract the year part of a date
Use the MONTH function to extract the month part of a date
Use the DAY function to extract the day part of a date
Use the MONTH and DAY functions to sort birthdays by month
Use the DATE function to add months to a date
Use the EOMONTH function to determine the last day of a month
Use the DAYS360 function to calculate with a 360-day year
Use the WEEKDAY function to calculate with different hourly pay rates
Use the WEEKNUM function to determine the week number
Use the EDATE function to calculate months
Use the WORKDAY function to calculate workdays
Use the NETWORKDAYS function to determine the number of workdays
Use the YEARFRAC function to calculate ages of employees
Use the DATEDIF function to calculate the ages of employees
Use the WEEKDAY function to calculate the weeks of Advent
Use the TIMEVALUE function to convert text to time
Use a custom format to create a time format
Use the HOUR function to calculate with 100-minute hours
Use the TIME function to combine single time parts
Chapter 5 -- Basic Statistical Functions
Use the MAX function to determine the largest value in a range
Use the MIN function to find the employee with the lowest sales
Use the MIN function to detect the smallest value in a column
Use the SMALL function to find the smallest values in a list
Use the LARGE function to find the highest values
Use the INDEX, MATCH, and LARGE functions to determine and locate the best
salesperson
Use the SMALL function to compare prices and select the cheapest offer
Use the AVERAGE function to calculate the average output
Use the SUBTOTAL function to sum a filtered list
Use the COUNT function to count cells containing numeric data
Use the COUNTA function to count cells containing data
Use the COUNTA function to count cells containing text
Use the COUNTBLANK function to count empty cells
Use the COUNTA function to determine the last filled row
Use the SUBTOTAL function to count rows in filtered lists
Use the RANK function to determine the rank of sales
Use the MEDIAN function to calculate the average
Use the QUARTILE function to calculate the average
Use the STDEV function to determine the standard deviation
Chapter 6 -- Mathematical Functions
Use the SUM function to sum a range
Use the SUM function to sum several ranges
Use the SUMIF function to determine sales of a team
Use the SUMIF function to sum costs higher than \$1000
Use the SUMIF function to sum costs up to a certain date
Use the COUNTIF function to count phases that cost more than \$1000
Use the COUNTIF function to calculate an attendance list
Use the SUMPRODUCT function to calculate the value of the inventory
Use the SUMPRODUCT function to sum sales of a particular team
Use the SUMPRODUCT function to multiply and sum at once
Use the ROUND function to round numbers
Use the ROUNDDOWN function to round down numbers
Use the ROUNDUP function to round numbers up
Use the ROUND function to round time values to whole minutes
Use the ROUND function to round time values to whole hours
Use the MROUND function to round prices to 5 or 25 cents
Use the MROUND function to round values to the nearest multiple of 10 or 50
Use the CEILING function to round up values to the nearest 100
Use the FLOOR function to round down values to the nearest 100
Use the PRODUCT function to multiply values
Use the PRODUCT function to multiply conditional values
Use the QUOTIENT function to return the integer portion of a division
Use the POWER function to calculate the square and cube roots
Use the POWER function to calculate interest
Use the MOD function to extract the remainder of a division
Modify the MOD function for divisors larger than the number
Use the ROW function to mark every other row
Use the SUBTOTAL function to perform several operations
Use the SUBTOTAL function to count all visible rows in a filtered list
Use the RAND function to generate random values
Use the RANDBETWEEN function to generate random values in a specified range
Use the EVEN and ODD functions to determine the nearest even/odd value
Use the ISEVEN and ISODD functions to check if a number is even or odd
Use the ISODD and ROW functions to determine odd rows
Use the ISODD and COLUMN functions to determine odd columns
Use the ROMAN function to convert Arabic numerals to Roman numerals
Use the SIGN function to check for the sign of a number
Use the SUMSQ function to determine the square sum
Use the GCD function to determine the greatest common divisor
Use the LCM function to determine the least common multiple
Chapter 7 -- Basic Financial Functions
Use the SYD function to calculate depreciation
Use the SLN function to calculate straight-line depreciation
Use the PV function to decide how much to invest
Use the PV function to compare investments
Use the DDB function to calculate using the double-declining balance method
Use the PMT function to determine the payment of a loan
Use the FV function to calculate total savings account balance
Use the RATE function to calculate the interest rate
Chapter 8 -- Database Functions
Use the DCOUNT function to count special cells
Use the DCOUNT function to count cells in a range between X and Y
Use the DCOUNTA function to count all cells beginning with the same character
Use the DGET function to search for a product number
Use the DMAX function to find the most expensive product in a category
Use the DMIN function to find the least expensive product
Use the DMIN function to find the oldest person on a team
Use the DSUM function to sum sales of a period
Use the DSUM function to sum all prices of a category that are above a particular level
Use the DAVERAGE function to determine the average price of a category
Chapter 9 -- Lookup and Reference Functions
Use the ADDRESS, MATCH, and MAX functions to find the largest number
Use the ADDRESS, MATCH, and MIN functions to find the smallest number
Use the ADDRESS, MATCH, and TODAY functions to sum sales up to today's date
Use the VLOOKUP function to look up and extract data from a database
Use the VLOOKUP function to compare offers from different suppliers
Use the HLOOKUP function to determine sales and costs of a team
Use the HLOOKUP function to determine sales for a particular day
Use the HLOOKUP function to generate a list for a specific month
Use the LOOKUP to get the directory of a store
Use the LOOKUP to get the indicator for the current temperature
Use the INDEX function to search for data in a sorted list
Use the INDIRECT function to play "Battle Ship"
Use the INDIRECT function to copy cell values from different worksheets
Use the INDEX function to determine the last number in a column
Use the INDEX and COUNTA functions to determine the last number in a row
Use the OFFSET function to sum sales for a specified period
Use the OFFSET function to consolidate sales for a day
Use the OFFSET function to filter every other column
Use the OFFSET function to filter every other row
Use the HYPERLINK function to jump directly to a cell inside the current worksheet
Use the HYPERLINK function to link to the Internet
Chapter 10 -- Conditional Formatting with Formulas
Use the WEEKDAY function to determine weekends and shade them
Use the TODAY function to show actual sales
Use conditional formats to indicate unavailable products
Use the TODAY function to shade a special column
Use the WEEKNUM and MOD functions to shade every other Tuesday
Use the MOD and ROW functions to shade every third row
Use the MOD and COLUMN functions to shade every third column
Use the MAX function to find the largest value
Use the LARGE function to find the three largest values
Use the MIN function to find the month with the worst performance
Use the MIN function to search for the lowest non-zero number
Use the COUNTIF function to mark duplicate input automatically
Use the COUNTIF function to check whether a number exists in a range
Use conditional formatting to control font styles in a specific range
Use a user-defined function to detect cells with formulas
Use a user-defined function to detect cells with numeric values
Use the EXACT function to perform a case-sensitive search
Use the SUBSTITUTE function to search for text
Use conditional formatting to shade project steps with missed deadlines
Use conditional formatting to create a Gantt chart in Excel
Use the OR function to indicate differences higher than 5% and lower than -5%
Use the CELL function to detect unlocked cells
Use the COUNTIF function to shade matching numbers in column B
Use the ISEEROR function to mark errors
Use the DATEDIF function to determine all friends younger than 30
Use the MONTH and TODAY functions to determine all birthdays that fall in the current
month
Use conditional formatting to border summed rows
Use the LEFT function to create a special product search
Use the AND function to detect empty rows in a range
Chapter 11 -- Working with Array Formulas
Use the ADDRESS, MAX, and ROW functions to determine the last used cell
Use the INDEX, MAX, ISNUMBER, and ROW functions to determine the last number
in a column
Use the INDEX, ISNUMBER, MAX, and COLUMN functions to determine the last
number in a row
Use the MAX, IF, and COLUMN functions to determine the last used column in a range
Use the MIN and IF functions to find the lowest non-zero value in a range
Use the AVERAGE and IF functions to calculate the average of a range, taking zero
values into consideration
Use the SUM and IF functions to sum values with several criteria
Use the INDEX and MATCH functions to search for a value that matches two criteria
Use the SUM function to count values that match two criteria
Use the SUM function to count values that match several criteria
Use the SUM function to count numbers from X to Y
Use the SUM and DATEVALUE functions to count today's sales of a specific product
Use the SUM function to count today's sales of a specific product
Use the SUM, OFFSET, MAX, IF, and ROW functions to sum the last row in a dynamic
list
Use the SUM, MID, and COLUMN functions to count specific characters in a range
Use the SUM, LEN, and SUBSTITUTE functions to count the occurrences of a specific
word in a range
Use the SUM and LEN functions to count all digits in a range
Use the MAX, INDIRECT, and COUNT functions to determine the largest gain/loss of
shares
Use the SUM and COUNTIF functions to count unique records in a list
Use the AVERAGE and LARGE functions to calculate the average of the x largest
numbers
Use the TRANSPOSE and OR functions to determine duplicate numbers in a list
Use the MID, MATCH, and ROW functions to extract numeric values from text
Use the MAX and COUNTIF functions to determine whether all numbers are unique
Use the TRANSPOSE function to copy a range from vertical to horizontal or vice versa
Use the FREQUENCY function to calculate the number of sold products for each group
Chapter 12 -- Special Solutions with Formulas
Use the COUNTIF function to prevent duplicate input through validation
Use the EXACT function to allow only uppercase characters
Use validation to prevent data input by a specific criterion
Use controls with formulas
Goal Seek as a powerful analysis tool
Use a custom function to shade all cells containing formulas
Use a custom function to change all cells with formulas to values
Use a custom function to document and display all cells containing formulas
Use a custom function to delete external links in a worksheet
Use a custom function to delete external links in a workbook
Use a custom function to enter all formulas into an additional worksheet
Chapter 13 -- User-defined Functions
Use a user-defined function to copy the name of a worksheet into a cell
Use a user-defined function to copy the name of a workbook into a cell
Use a user-defined function to get the path of a workbook
Use a user-defined function to get the full name of a workbook
Use a user-defined function to determine the current user of Windows or Excel
Use a user-defined function to display formulas of a specific cell
Use a user-defined function to check whether a cell contains a formula
Use a user-defined function to check whether a cell contains data validation
Use a user-defined function to find all comments
Use a user-defined function to sum all shaded cells
Use a user-defined function to sum all cells with a shaded font
Use a user-defined function to delete leading zeros for specified cells
Use a user-defined function to delete all letters in specified cells
Use a user-defined function to delete all numbers in specified cells
Use a user-defined function to determine the position of the first number
Use a user-defined function to calculate the cross sum of a cell
Use a user-defined function to sum each cell's cross sum in a range
Use a user-defined function to check whether a worksheet is empty
Use a user-defined function to check whether a worksheet is protected
Use a user-defined function to create your own AutoText
Chapter 14-Examples
Calculating average fuel consumption
Calculating net prices and corresponding gross prices
Determining the economic value of a product
Calculating the final price of a product, taking rebates and price reductions into account
Searching for data that meets specific criteria
Separating cities from zip codes
Eliminating specific characters
Combining text, dates, and timestamps
Determining the last day of a month
Determining the number of actual workdays
Determining a person's exact age
Determining the number of values in a specific range
Determining the weekly sales for each department
Rounding a value to the nearest 5 cents
Determining the inventory value
Determining the top salesperson for a month
Determining the three highest values in a list
Determining the amount to invest
Determining how many items are in various categories
Finding a specific value in a complex list
Dynamically showing costs and sales per day
Extracting every fourth value from a list
Index
```

Library of Congress Subject Headings for this publication:

Microsoft Excel (Computer file).
Business -- Computer programs.