LM

EXCEL_Formulas_Bible

Introduction to the Excel Formulas Bible

  • The guide is titled Excel Formulas Bible and is relevant for Excel 365/Excel 2021.

  • Updated to include Dynamic Arrays and many new formulas.

Copyright Information

  • Document is copyright free.

  • Free to distribute and use without permission.

  • Contact for queries: Vijay A. Verma at e@eforexcel.com.

Table of Contents Overview

  • The document covers a wide range of formulas organized into numbered topics, including:

    • SUM Calculations

    • Date and Time Functions

    • Text Manipulation

    • Statistical Functions

    • Financial Functions

    • Conditional Calculations

Detailed Discussion of Selected Topics

1. SUM of Digits from Cell with Only Numbers

  • To calculate the sum of digits in a cell that contains only numbers use:

    =SUM(--MID(A1,SEQUENCE(LEN(A1)),1))

  • Utility of other implementations like SUMPRODUCT may also work for similar results.

2. Handling Cells with Mixed Content (Numbers + Non-numbers)

  • Formulas can return a sum of digits while ignoring non-numeric characters:

    • Variants available using SUMPRODUCT.

    • Enclose in IFERROR to manage empty cell entries effectively.

3. Identifying Uniqueness of List Items

  • Formula to check for duplicates in a range (e.g., A1:A1000):

    =MAX(COUNTIF(A1:A1000,A1:A1000))

  • Returns 1 for uniqueness and anything above indicates duplicates.

4. Date Calculations (Last and First Day of Month)

  • To find the last day of the month given a date in A1:

    =EOMONTH(A1,0)

  • For the first day:

    =A1-DAY(A1)+1

5. Financial Functions

  • To calculate EMIs, utilize the PMT function format:

    =PMT(rate, nper, pv, [fv], [type])

  • Example for interest portion calculation:

    =IPMT(rate, per, nper, pv, [fv], [type])

6. Unique List Extraction

  • To generate a unique list from duplicates in a range, use:

    =UNIQUE(A2:A100)

  • Older techniques may involve nested INDEX/MATCH combinations.

7. Calculating Age from a Birthday

  • To compute age based on a birthday in A1:

    =DATEDIF(A1,TODAY(),"y")&" Years "&DATEDIF(A1,TODAY(),"ym")&" Months "&DATEDIF(A1,TODAY(),"md")&" Days"

8. Working with Conditional Counts

  • Example of counting entries meeting certain criteria using COUNTIFS.

  • The formula logic can be adjusted for dynamic criteria, e.g.

    =SUM(COUNTIFS(A2:A10, "criteria1", B2:B10, "criteria2"))

9. Generating Random Unique Numbers

  • To generate non-repeating random numbers between specific bounds, use:

    =INDEX(UNIQUE(RANDARRAY(30-1+1,,1, 30, TRUE)), SEQUENCE(10))

Summary

  • Each section combines specific purpose formulas, providing flexibility and utility across various tasks in Excel including finance, text manipulation, data management, and problem-solving with analytics.

  • Resources and supplementary downloads can greatly enhance practical applications of the formulas in live projects and personal use.