Microsoft Excel functions and formulas : with Excel 2019 / Office 365 / Bernd Held, Brian Moriarty, Theodor Richardson.

Format:

Book

Author/Creator:

Held, Bernd, author.

Moriarty, Brian, author.

Richardson, Theodor, author.

Language:

English

Subjects (All):

Microsoft Excel (Computer file).

Business--Computer programs.

Business.

Electronic spreadsheets.

Physical Description:

1 online resource (496 pages)

Edition:

Fifth edition.

Place of Publication:

Dulles, Virginia : Mercury Learning and Information, [2019]

Summary:

In this completely updated edition covering Excel 2019, previous versions, and Office 365, Microsoft Excel Functions & Formulas 5/E demonstrates the secrets of Excel through the use of practical and useful examples in a quick reference format. 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. The book's extensive examples and added video tutorials make it an excellent resource for all Excel users who want to understand, create, and apply formulas. Experienced users will also find Microsoft Excel Functions & Formulas 5/E an excellent reference for many of the program's advanced formulas and functions. The text is easy to understand with numerous step-by-step instructions and the actual, ready to use, Excel screenshots of the input and output from the formulas. The book includes companion files with video tutorials, over 250 worksheet files of examples for numerous functions, formulas, and all the figures from the text. FEATURES * Includes companion files with video tutorials, over 250 Excel worksheet examples, and all of the figures from the text (including 4-color) * Completely updated to cover Microsoft Excel 2019, previous versions, and Office 365 * Saves hundreds of hours with the latest Excel tips, worksheets, and shortcuts * Written by a proven author with Microsoft Valued Professional (MVP) status The Companion Files are also available for downloading from the publisher by emailing proof of purchase to info@merclearning.com.

Contents:

Intro

Microsoft® Excel® Functions and Formulas

CONTENTS

ACKNOWLEDGMENTS

INTRODUCTION

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

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

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 (*NEW IN EXCEL 2016*)

Use the IF function to compare two cells

Use the IFS function to compare two cells(*NEW IN EXCEL 2016*)

Use the SWITCH function to compare two cells(*NEW IN EXCEL 2016*)

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(*NEW IN EXCEL 2016*)

Use SWITCH function to cover multiple possibilities (*NEW IN EXCEL 2016*)

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

3 Text Functions

Use the LEFT and RIGHT functions to separate a textstring 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 partsof 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

4 Date and TimeFunctions

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

5 Basic Statistical Functions

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 function to determine the rank of sales

Use the MEDIAN function to calculate the median sales

Use the QUARTILE function to calculate the quartiles

Use the STDEV function 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

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 function to round up prices to the nearest 100.

Notes:

Description based on print version record.

ISBN:

9781523140022

152314002X

9781683923749

168392374X