
Microsoft Excel Functions and Formulas
Description
Alles über E-Books | Antworten auf Fragen rund um E-Books, Kopierschutz und Dateiformate finden Sie in unserem Info- & Hilfebereich.
All prices
More details
Other editions
Additional editions


Previous edition

Content
- Cover
- Half-Title
- Title
- Copyright
- Contents
- Acknowledgments
- Introduction
- Chapter 1: Formulas in Excel
- Calculate 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 weights
- The quick calendar
- Design your own to-do list
- Increment row numbers
- Convert negative values to positive
- Calculate sales taxes
- Combine text and numbers
- Combine text and date
- Combine text and time
- Generate a special ranking list
- Determine average output
- Determine stock gains and losses
- Evaluate profitability
- Determine percentage of completion
- Convert miles per hour to kilometers 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
- Calculate total with percentage
- Monitor the daily production plan
- Calculate the number of hours between two dates
- Determine the price per pound
- Determine how many pieces to put in a box
- Calculate the number of employees required for a project
- Distribute sales
- Calculate your net income
- Calculate the percentage of price reduction
- Divide and double every three hours
- Calculate the average speed
- Calculate number of characters in a string
- 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 XOR function to check for mutually exclusive conditions
- 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 IFS function to calculate the commissions for individual sales
- Use the IF function to compare two cells
- Use the IFS function to compare two cells
- Use the SWITCH function to compare two cells
- Use the INT function with the IF function
- Use the TYPE function to check for invalid values
- Use nested IF functions to cover multiple possibilities
- Use IFS function to cover multiple possibilities
- Use SWITCH function to cover multiple possibilities
- Use the IF function to check whether a date is in the past or the future
- Use the IF function to create your own timesheet
- Use the IFERROR function to display a default
- 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 characters
- 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 columns
- 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 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 non-printable 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 UNICHAR function to determine the Unicode character from a number
- Use the UNICODE function to determine the numeric Unicode value of a character
- Use the DOLLAR function to convert numbers to currency in text format
- Use the T function to check for valid numbers
- Use the TEXT function to combine and format text
- Use CONCATENATE function to combine text
- Use CONCAT function to combine text
- Use TEXTJOIN function to combine text
- Use ARRAYTOTEXT function to combine text
- Use VALUETOTEXT function to display text from any cell value
- Chapter 4: Date and Time Functions
- Use custom formatting to display 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 function 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 recalculate dates formatted as text
- 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 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 discover the lowest sales volume for a month
- Use the MINIFS function to discover the lowest sales volume for a month based on criteria
- Use the MAXIFS function to discover the highest sales volume for a month based on criteria
- 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, RANK.EQ, RANK.AVG functions to determine the rank of sales
- Use the MEDIAN function to calculate the median sales
- Use the QUARTILE, QUARTILE.INC, QUARTILE.EXC functions to calculate quartiles
- Use the STDEV, STDEV.S, STDEV.P functions to determine the standard deviation
- Use the FORECAST.LINEAR function to determine future values
- Use the FORECAST.ETS function to determine future values
- Use the FORECAST.ETS.CONFINT function to determine confidence in future values
- Use the FORECAST.ETS.SEASONALITY function to future value patterns
- Use the CORREL function to determine data correlation
- Use the AVERAGE, GEOMEAN, HARMEAN, TRIMMEAN to determine meaningful averages
- 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 $1,000
- Use the SUMIF function to sum costs up to a certain date
- Use the COUNTIF function to count phases that cost more than $1,000
- 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 team
- Use the SUMPRODUCT function to multiply and sum at the same time
- Use the ROUND function to round numbers
- Use the ROUNDDOWN function to round numbers down
- 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/CEILING.MATH functions to round up prices to the nearest $100
- Use the FLOOR/FLOOR.MATH function to round down prices 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 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 RANDARRAY function to generate random values in a range of rows and columns
- 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 ARABIC function to convert Roman numerals to Arabic numerals
- Use the BASE function to convert decimal numbers to binary numbers
- Use the DECIMAL function to convert binary numbers to decimal numbers
- 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
- Use the SUMIFS function to determine sales of a team and gender of its members
- Use the COUNTIFS function to count phases that cost more than $1,000 within a certain duration
- Compare the INT, TRUNC, and ROUND functions
- Use the ABS function to return only positive differences between numbers
- Use the SQRT function to determine the hypotenuse of a right triangle
- Use the COMBIN function to determine the number of combinations in a lottery
- Use the FACT function to determine the number of combinations in a lottery
- Use the LET and IFS functions to determine sales bonuses
- Use the SEQUENCE function to generate a sequential list of dates in a row, column, or an array
- 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 an amount 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 interest rate
- Use the INTRATE function to calculate interest over an entire period
- Use the NOMINAL function to calculate real interest rate given an annual 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 by number
- Use the DMAX function to find the most expensive product in a category
- Use the DMIN function to find the least expensive product in a category
- Use the DMIN function to find the oldest person on a team
- Use the DSUM function to sum sales for a period
- Use the DSUM function to sum all prices in a category that are above a particular level
- Use the DAVERAGE function to determine the average price in a category
- Chapter 9: Lookup and Reference Functions
- Use the ADDRESS, MATCH, and MAX functions to find the position of the largest number
- Use the ADDRESS, MATCH, and MIN functions to find the position of the smallest number
- Use the ADDRESS, MATCH, and TODAY functions to sum sales up to today's date
- Use the XMATCH function to find who sold cookie boxes at or near the goal
- Use the VLOOKUP function to look up and extract data from a database
- Use the XLOOKUP function to return a row of information
- Use the VLOOKUP function to compare offers from different suppliers
- Use the HLOOKUP function to determine sales and costs for 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 function to get the directory of a store
- Use the LOOKUP function 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 "Battleship"
- 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
- Use the CHOOSE function to lookup values
- Use the ROW and COLUMN functions to determine how many rows and columns are in a worksheet
- Use the FORMULATEXT function to display a formula in a cell
- Use the TRANSPOSE function to rearrange information on a worksheet
- Use the FILTER function to filter data from a range
- Use the SORT function to sort an existing range into another range
- Use the SORTBY function to sort an existing range into another range
- Use the UNIQUE function to return unique rows of data into another range
- Chapter 10: Conditional Formatting with Formulas
- Use the WEEKDAY function to determine weekends and shade them
- Use the TODAY function to show completed sales
- Use conditional formats to indicate unavailable products
- Use the TODAY function to shade a specific 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 nonzero 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 valid 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 ISERROR function to mark errors
- Use the DATEDIF function to determine all friends younger than 30
- Use the MONTH and TODAY functions to find birthdays in the current month
- Use conditional formatting to border summed rows
- Use the LEFT function in a product search
- Use the AND function to detect empty rows in a range
- Use the COUNTIFS function to determine value based on multiple filters
- Chapter 11: Working with Dynamic Array Formulas
- Use the ADDRESS, MAX, and ROW functions to determine the last cell used
- Use the INDEX, MAX, ISNUMBER, and ROW functions to find the last number in a column
- Use the INDEX, MAX, ISNUMBER, and COLUMN functions to find the last number in a row
- Use the MAX, IF, and COLUMN functions to determine the last column used in a range
- Use the MIN and IF functions to find the lowest nonzero 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 UNIQUE function to list 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 products sold for each group
- Use the FILTER function to determine wins for a specific division
- Use the SORT function to list an existing table of data by a certain column
- Use the SORTBY function to list an existing table of data by a certain column
- Use the RANDARRAY function to return a list of random numbers across any number of rows and columns
- Use the SEQUENCE function to return a list of a sequence of numbers across any number of rows and columns
- 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 data validation to allow data input by a specific criterion
- Use data validation to limit data to a specific list
- Use controls with formulas
- Use Goal Seek as a powerful analysis tool
- Use a custom function to shade all cells containing formulas
- Use a custom function to change all formulas in cells 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 colored 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
- Calculate average fuel consumption
- Calculate net and corresponding gross prices
- Determine the economic value of a product
- Calculate the final price of a product, taking into account rebates and price reductions
- Search for data that meets specific criteria
- Separate cities from zip codes
- Eliminate specific characters
- Combine text, dates, and timestamps
- Determine the last day of a month
- Determine the number of available workdays
- Determine a person's exact age
- Determine the number of values in a specific range
- Determine the weekly sales for each department
- Round a value to the nearest 5 cents
- Determining inventory value
- Determine the top salesperson for a month
- Determine the three highest values in a list
- Determine amount to invest
- Determine how many items are in various categories
- Find a specific value in a complex list
- Dynamically show costs and sales per day
- Extract every fourth value from a list
- Display names in a list or in an array
- Filter for certain job positions
- Display the mile time for a runner
- Create a list of random telephone numbers
- Create a list of sequential Friday dates
- Sort a list of names
- Sort a list of names to include all their data
- List unique data based on names and city
- Retrieve student's math and history scores
- Retrieve student's total score
- Chapter 15: Other Features
- Insert Icons
- Draw Freestyle
- Smart Lookup
- Share Files
- Flash Fill
- Quick Analysis Tool
- Map Chart
- Funnel Chart
- Data Types - Stocks
- Data Types - Geography
- View workbook objects using the navigation pane
- Review workbook statistics
- Smooth scrolling when cells cross over viewable pages
- Unhiding multiple worksheets at once
- Resizing the conditional formatting window
- Duplicating a conditional formatting rule
- Chapter 16: Data Analytics Using Excel
- Activate "Analysis ToolPak"
- Display a Histogram to view the frequency of Sales
- Display many statistics describing a data set
- Reveal how numbers correlate to each other
- Smooth a natural progression of numbers
- Determine a moving average for a data set
- Create a list of random numbers
- Rank a list of clients obtained in a month
- Determine an accurate house price
- Select four random customers to receive a prize
- Chapter 17: Shortcut Keys
- Go to cells or highlight cell ranges
- Other Common Shortcuts
- Appendix: Excel Interface Guide
- Anatomy of Excel
- Microsoft 365 vs 2021
- Microsoft Excel 2021
- Microsoft Excel 2021 for Macintosh
- Microsoft Excel Web App
- Index
System requirements
File format: PDF
Copy protection: Watermark-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Use the free software Adobe Reader, Adobe Digital Editions, or any other PDF viewer of your choice (see eBook Help).
- Tablet/Smartphone (Android; iOS): Install the free app Adobe Digital Editions or another reading app for eBooks, e.g., PocketBook (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (only limited: Kindle).
The file format PDF always displays a book page identically on any hardware. This makes PDF suitable for complex layouts such as those used in textbooks and reference books (images, tables, columns, footnotes). Unfortunately, on the small screens of e-readers or smartphones, PDFs are rather annoying, requiring too much scrolling.
This eBook uses Watermark-DRM, a „soft” copy protection. This means that there are no technical restrictions to prevent illegal distribution. However, there is a personalised watermark embedded in the eBook that can be used to identify the purchaser of the eBook in the event of misuse and to provide evidence for legal purposes.
For more information, see our eBook Help page.
File format: PDF
Copy-Protection: Adobe-DRM (Digital Rights Management)
System requirements:
- Computer (Windows; MacOS X; Linux): Install the free reader Adobe Digital Editions prior to download (see eBook Help).
- Tablet/smartphone (Android; iOS): Install the free app Adobe Digital Editions or the app PocketBook before downloading (see eBook Help).
- E-reader: Bookeen, Kobo, Pocketbook, Sony, Tolino and many more (only limited: Kindle).
The file format PDF always displays a book page identically on any hardware. This makes PDF suitable for complex layouts such as those used in textbooks and reference books (images, tables, columns, footnotes). Unfortunately, on the small screens of e-readers or smartphones, PDFs are rather annoying, requiring too much scrolling.
This eBook uses Adobe-DRM, a „hard” copy protection. If the necessary requirements are not met, unfortunately you will not be able to open the eBook. You will therefore need to prepare your reading hardware before downloading.
Please note: We strongly recommend that you authorise using your personal Adobe ID after installation of any reading software.
For more information, see our eBook Help page.