Microsoft Excel Functions & Formulas

Microsoft Excel Functions & Formulas

by Bernd Held
Released September 2006
Publisher(s): Jones & Bartlett Learning
ISBN: 9781449612993

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Easy to use and equipped with a variety of functions, Microsoft Excel is the tool of choice when it comes to crunching numbers, building charts, and analyzing tables. But most users only scratch the surface of the program’s functionality, especially the built-in formulas designed to make everyday operations and real-world tasks more efficient. Microsoft Excel Functions & Formulas demonstrates the secrets of Excel functions through the use of practical and useful examples in a quick reference format. Topics include logical, text, date and time, basic statistical, mathematical, financial, database, and lookup and reference functions as well as conditional formatting with formulas, array formulas, and user-defined functions. This book’s extensive examples make it an excellent tutorial for all Excel users who want to understand, create, and apply formulas. Intermediate and advanced users will find Microsoft Excel Functions & Formulas an excellent reference to many of the program’s advanced formulas and functions.

Table of contents

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

Product information

  • Title: Microsoft Excel Functions & Formulas
  • Author(s): Bernd Held
  • Release date: September 2006
  • Publisher(s): Jones & Bartlett Learning
  • ISBN: 9781449612993