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.