Scribd
Upload
4 views

New Excel Functions

The document provides an overview of various Excel functions, including Regular Expressions, Text Manipulation, Array Manipulation, Data Selection, and D-related database functions. It includes examples of how to use functions like REGEXEXTRACT, DGET, and DCOUNT for data extraction and manipulation. Additionally, it highlights use cases for cleaning datasets and performing dynamic calculations based on specific criteria.

Uploaded by

দিগন্তের ওপারে
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views

New Excel Functions

The document provides an overview of various Excel functions, including Regular Expressions, Text Manipulation, Array Manipulation, Data Selection, and D-related database functions. It includes examples of how to use functions like REGEXEXTRACT, DGET, and DCOUNT for data extraction and manipulation. Additionally, it highlights use cases for cleaning datasets and performing dynamic calculations based on specific criteria.

Uploaded by

দিগন্তের ওপারে
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 12

Excel Formulas

Regular Expressions (Regex) Functions

Use Case:
1. REGEXTRACT:
If cell A1 contains "Contact: john.doe@example.com", the formula
=REGEXEXTRACT(A1, "\b[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}\b") will
return "john.doe@example.com".

2. REGEXREPLACE:

3. REGEXTEST
Text Manipulation Functions

1.TEXT BEFORE:

2. TEXTAFTER:

3. TEXTSPLIT:

4. TRANSLATE:
5. DETECTLANGUAGE:

Array Manipulation Functions

1.​ VSTACK:
2.​ HSTACK:

3.​ TOROW:

4.​ TOCOL:

5.​ WRAPROWS:
Data Selection and Modification

1.​ TAKE:

2.​ DROP:
3.​ CHOOSEROWS:

4.​ CHOOSECOLS:

Data Aggregation and Analysis

1.​ GROUPBY:
2.​ PIVOTBY:

3.​ TRIMRANGE:

Use Case: Cleaning up datasets by eliminating unnecessary empty spaces.

Example:

If range A1:D10 has empty rows and columns at its borders,


=TRIMRANGE(A1:D10) returns the range without those empty rows and
columns.

Custom Function Creation


1.​ LET:

2.​ LAMBDA:

AI-Powered Functions

1.​ AI.PREDICT:

2.​ AI.SUMMARIZE:
Cloud and API-Driven Functions

1.​ CALLAPI:
Excel's DGET function and other D-related database functions (often
referred to as D-functions) are used to extract and manipulate data from a
structured dataset (or table) based on specific criteria.

1. DGET Function

Syntax:

DGET(database, field, criteria)

Explanation:

●​ database: The entire dataset or table, including headers.


●​ field: The column name (in quotes) or column index from which to
retrieve the value.
●​ criteria: A range specifying the condition(s) to apply.

Example:

If you have a table (A1:D10) containing employee details and want to get
the salary of an employee named "John":

=DGET(A1:D10, "Salary", F1:G2)

●​ Assuming F1:G2 contains criteria like:


●​ Name​| John

This function retrieves John's salary from the database.

2. Other D-Related Functions in Excel

These functions work similarly to DGET, operating on a dataset using


criteria.

Function Description
DAVERAG Returns the average of a column based on given
E criteria.

DCOUNT Counts numeric entries in a column based on criteria.

DCOUNTA Counts non-empty cells (text or numbers) in a column


based on criteria.

DMAX Returns the maximum value in a column that meets the


criteria.

DMIN Returns the minimum value in a column that meets the


criteria.

DPRODUC Multiplies values in a column that meet the criteria.


T

DSTDEV Estimates the standard deviation based on a sample.

DSTDEVP Calculates the standard deviation for an entire


population.

DSUM Returns the sum of a column based on criteria.

DVAR Estimates the variance based on a sample.

DVARP Calculates the variance for an entire population.

Example Uses

1.​ Calculate total sales where Region = "West"


2.​ =DSUM(A1:D10, "Sales", F1:G2)
3.​ Find the highest salary for a specific department
4.​ =DMAX(A1:D10, "Salary", F1:G2)
5.​ Count employees in a department
6.​ =DCOUNT(A1:D10, "Employee ID", F1:G2)

When to Use D-Functions?

●​ When working with structured databases/tables.


●​ When needing dynamic calculations based on criteria.
●​ When requiring single-value results (like in DGET).

Would you like a hands-on example in an Excel file? I can create one for
you!

You might also like