Advanced excel formulas : unleashing brilliance with excel formulas / / Alan Murray

• Autore: Murray Alan
• Pubblicazione: New York : , : Apress, , [2022]; ©2022
• Descrizione fisica: 1 online resource (834 pages)
• Disciplina: 005.54
• Soggetto topico: Electronic spreadsheets - Computer programs
• Note generali: Includes index.
• Nota di bibliografia: Includes index.
• Nota di contenuto: Intro -- Table of Contents -- About the Author -- About the Technical Reviewer -- Acknowledgments -- Introduction -- Chapter 1: Excel Formulas: A Quick Primer -- Anatomy of an Excel Formula -- Breakdown of a Function -- Table of Formula Characters -- Order of Calculation -- Order of Range Operations -- Order of Mathematical Operations -- The Function Wizard -- Cell Referencing -- Absolute References -- Relative References Explained -- Making a Reference Absolute -- Mixed References -- Sheet References -- Workbook References -- Calculating Percentages -- Percentage of a Value -- Percentage of a Total -- Increase a Value by a Percentage -- Decrease a Value by a Percentage -- Calculate Percentage Change -- When Formulas Go Wrong -- Formula Errors -- Automatic Error Checking -- Excel Formula Is Not Calculating -- Formula Calculations Are Set to Manual -- Show Formulas Is Switched On -- Formula Is Stored As Text -- Cell Formatted As Text -- Summary -- Chapter 2: Logical Functions -- What Is TRUE and FALSE? -- The IF Function -- Example 1: Testing Numeric Values -- Example 2: Testing Text Values -- Example 3: Exact Text Match -- Example 4: Partial Text Match -- Example 5: Testing Date Values -- Example 6: Testing If a Cell Is Empty or Blank -- Example 7: Return a Symbol or Emoji -- Nested IF Functions -- IFS Function -- AND, OR, XOR, and NOT Functions -- AND Function -- OR Function -- XOR Function -- NOT Function -- The SWITCH Function -- Example 1: Match Against a List of Text Values -- Example 2: Evaluate to TRUE -- Example 3: Match a Formula Result to the List of Values -- Example 4: Pick Formula from a Drop-Down List -- Handling Errors -- IFERROR Function -- IFNA Function -- Logical Functions with Excel Features -- Format Values That Meet a Goal -- Conditional Formatting Rule with Multiple Conditions. ; Data Validation Rule Based on Another Cell Value -- Conditional Formatting with Charts -- Dynamic Chart Data with SWITCH -- Summary -- Chapter 3: Defined Names -- Named Ranges -- Why Use Named Ranges? -- Define a Named Range with the Name Box -- Using Named Ranges in Formulas -- Rules for Defining Names -- Tips for Defining Names -- Named Ranges with Multiple Cells -- Named Range for a Drop-Down List -- Define a Dynamic Named Range -- Define Names with Create from Selection -- Scope of a Defined Name -- Define a Named Constant -- Finding Named Ranges -- Relative Named Ranges -- Named Formulas -- Managing Names -- Edit a Name -- Filtering Names -- Print a List of the Names -- Apply Names to Existing Formulas -- Summary -- Chapter 4: You Need to Start Using Tables -- Why Use Tables? -- Format a Range As a Table -- Creating a Table -- Changing or Removing Table Styles -- Naming the Table -- Working with Tables -- Table References in a Formula -- The Magic of @ -- Referencing Table Elements -- Make Table Column and Cell References Absolute -- Tables with Other Excel Features -- Dynamic Lists -- Dynamic Charts -- Summary -- Chapter 5: Manipulating Text -- Extract Characters from a Text String -- LEFT and RIGHT -- Extract Characters from the Middle of a String -- FIND and SEARCH for Irregular Strings -- Example 1: Extract Characters Before a Delimiter -- Example 2: Extract Characters After a Delimiter -- Example 3: Combining LEFT and RIGHT -- Example 4: Extract Text Between Two Characters -- Example 5: Extract Number from a Text String -- Change the Case of Text -- The LEN Function -- Convert Text to a VALUE -- The NUMBERVALUE Function -- Remove Unwanted Characters -- The TRIM Function -- The CLEAN Function -- SUBSTITUTE and REPLACE Functions -- The REPLACE Function -- The SUBSTITUTE Function -- Example 1: Simple SUBSTITUTE Example. ; Example 2: Count the Number of Words in a Cell -- Example 3: Use the Instance Number to Extract Text -- Example 4: Return Text After the Last Delimiter -- TEXTBEFORE and TEXTAFTER Functions -- Example 1: Text Before or After the First Delimiter -- Example 2: Specify an Instance Number -- Example 3: Extract Text Between Two Characters -- Example 4: Using Match End and If Not Found -- The TEXTSPLIT Function -- Example 1: Simple TEXTSPLIT -- Example 2: Using Multiple Column Delimiters -- Example 3: Splitting Across Rows (and Columns) -- Example 4: Handling Missing Data -- Combine Text into One Cell -- The CONCATENATE Function -- The Ampersand Character -- The CONCAT Function -- The TEXTJOIN Function -- TEXTJOIN and Delimiters -- TEXTJOIN and Empty Values -- TEXTJOIN and Arrays -- The TEXT Function -- Example 1: Currency Formats -- Example 2: Positive and Negative Value Formats -- Example 3: Date Formats -- Repeating a Character - REPT Function -- Text Functions with Other Excel Features -- Conditional Formatting Rules - Last Character Equals -- Data Validation Rules - Forcing Correct Case -- Dynamic Labels for Charts -- Summary -- Chapter 6: Working with Dates and Time -- Understanding Dates and Time -- Dates and the Calendar System in Excel -- Calculate the Difference Between Two Dates -- Time in Excel -- Calculate the Difference in Two Times -- The TODAY and NOW Functions -- Convert Text to Date -- The DATEVALUE Function -- The VALUE Function -- The DATE Function -- Extract Date from a Date-Time Stamp -- The INT Function -- The TRUNC Function -- Extract Time from a Date-Time Stamp -- The MOD Function -- The YEAR, MONTH, and DAY Functions -- The NETWORKDAYS Function -- Calculate the Working Days Difference Between Two Dates -- The NETWORKDAYS.INTL Function -- Example 1: Customized NETWORKDAYS.INTL Function -- Example 2: Conditional Holiday Ranges. ; Example 3: Number of Fridays Between Two Dates -- The YEARFRAC Function -- The DATEDIF Function -- The WORKDAY Function -- Calculate a Due Date from a Starting Date -- The WORKDAY.INTL Function -- Calculate First Working Day of a Month -- EDATE and EOMONTH -- Calculate Contract End Dates -- Last Day of a Month -- Number of Days in a Month -- Last Working Day of a Month -- First Working Day of Next Month -- Returning Week Numbers -- The WEEKNUM Function -- What Is ISOWEEKNUM? -- Return the Week Number for Any Given Date -- Week Number in a Month -- The WEEKDAY Function -- Rate of Pay Determined by Day of Week -- Calculating Financial Years and Quarters -- Fiscal Years -- Indian Financial Year -- Australian Financial Year -- UK Financial Year -- Fiscal Quarters -- Working with Time -- Convert Text to Time -- The TIMEVALUE Function -- The TIME Function -- Rounding Time -- MROUND, CEILING.MATH, and FLOOR.MATH -- Round Time to the Nearest Hour -- Round Time to the Nearest 15 Minutes -- Time Difference Past Midnight -- Sum Hours over 24 Hours -- Handling Negative Time -- Date and Time Functions with Other Excel Features -- Highlight Approaching Due Dates -- Highlight Dates Due Within the Next Month -- Data Validation Rules - Prevent Weekend Entries -- Summary -- Chapter 7: The Infamous VLOOKUP Function -- Introduction to VLOOKUP -- VLOOKUP for an Exact Match -- VLOOKUP for a Range Lookup -- Column Index Number Tricks -- Trick 1: The COLUMN Function -- Trick 2: The COLUMNS Function -- Trick 3: Using an Index Row -- Reasons Why VLOOKUP Is Not Working -- Lookup Column Must Be the First Column -- Data Type of the Values Must Match -- Exact Match Has Not Been Specified -- Ranges Must Be in Ascending Order -- Handling Fake Errors with VLOOKUP -- The HLOOKUP Function -- Get VLOOKUP to Look to the Left -- Partial Match Lookup -- Case-Sensitive VLOOKUP. ; Multiple Column VLOOKUP -- Return the Nth Match -- VLOOKUP with Other Excel Features -- Conditional Formatting and VLOOKUP -- Dynamic Chart Range -- Summary -- Chapter 8: The Awesome SUMIFS, COUNTIFS, and Friends -- The SUMIFS Function -- SUMIF or SUMIFS -- Using Text Criteria -- Working with Multiple Criteria -- Using Numeric Criteria -- Sum Values Between Two Dates -- OR Logic with SUMIFS -- Using Wildcards with SUMIFS -- The Versatile COUNTIFS Function -- Comparing Two Lists -- Generating Unique Rankings -- Creating Conditional Rankings -- Creating a Frequency Distribution -- The AVERAGEIFS Function -- TRIMMEAN Function -- Conditional MEDIAN and MODE Functions -- Calculating the Median -- Calculating the Mode -- MAXIFS and MINIFS Functions -- Using the Functions with Other Excel Features -- Identifying Duplicates with Conditional Formatting -- Identifying Unmatched Values Between Two Lists -- Highlight Values Larger Than the Median -- Preventing Duplicate Entries -- Summary -- Chapter 9: Next-Level Aggregation Functions -- SUMPRODUCT Function -- Classic SUMPRODUCT -- Sum Values That Meet Multiple Criteria -- Count Values That Meet Multiple Criteria -- Aggregating Multiple Columns -- Distinct Count Formula -- Weighted Average -- Sum Every Nth Cell -- Sum Values for a Specific Weekday -- Sum the Top Five Values Only -- AGGREGATE Function -- Ignoring Hidden Rows -- Ignoring Errors in a Range -- Adding Criteria to AGGREGATE -- Function Specified by a Cell Value -- Summary -- Chapter 10: Dynamic Array Formulas -- Getting to Know Dynamic Array Formulas -- Arrays vs. Dynamic Arrays -- Dynamic Arrays with Tables -- Dynamic Array Formula Example -- Welcome the # Operator -- The #SPILL! Error -- DA Formulas Cannot Be Used Within Tables -- UNIQUE Function -- Returning Distinct and Unique Values -- UNIQUE with Multiple Columns -- UNIQUE to Compare Columns. ; Distinct Labels for SUMIFS.
• Sommario/riassunto: Enhance and upgrade your Excel knowledge with this comprehensive guide to formulas in Excel. Over 150 of the most useful Excel functions are covered with numerous practical examples of their use. This book is fully updated and includes examples of the most recently released functions in 2022.
• Titolo autorizzato: Advanced excel formulas
• ISBN: 1-4842-7125-4
• Formato: Materiale a stampa
• Livello bibliografico: Monografia
• Lingua di pubblicazione: Inglese
• Record Nr.: 9910590054103321
• Lo trovi qui: Univ. Federico II