The EOMONTH function with examples in Excel

The EOMONTH function in Excel is a powerful tool for calculating the last day of a month, either in the past or the future. Let’s explore how it works and look at some examples:

1. Syntax:

   • EOMONTH(start_date, months)
   • start_date: A valid Excel date representing the initial date.
   • months: The number of months before or after start_date.

2. Examples:

   • To find the last day of the current month, use:

     =EOMONTH(TODAY(), 0)

     (result: last day of the current month).
   • For the last day of the next month, use:

     =EOMONTH(TODAY(), 1)

     (result: last day of the next month).
   • To calculate the last day of the month three months ago, use:

     =EOMONTH(TODAY(), -3)

     (result: last day of the month, three months before today).

3. Additional Scenarios:

   • You can also move through years using EOMONTH. For example:
     • To find the last day of May 2018, use:

       =EOMONTH("2017-05-12", 12)

       (result: May 31, 2018).
     • To get the last day of May 2020, use:

       =EOMONTH("2017-05-12", 36)

       (result: May 31, 2020).

4. First Day of Current Month:

   • Although EOMONTH returns the last day of the month, you can use it to find the first day of the current month like this:

     =EOMONTH(TODAY(), -1) + 1

The EDATE function with examples in Excel

The EDATE function in Excel allows you to shift a date by a specified number of months, either into the future or the past. Here’s how it works:

1. Syntax:

   • EDATE(start_date, months)
   • start_date: The initial date (a valid Excel date).
   • months: The number of months to add or subtract.

2. Examples:

   • To find the date 6 months in the future from January 1, 2023, use:

     $$=EDATE("2023-01-01",6)$$

     (result: July 1, 2023).
   • For a date 4 months ago from today, use:

     $$=EDATE(TODAY(),-4)$$

     (result: 4 months before the current date).

3. Additional Scenarios:

   • To move by years, multiply the number of years by 12 (e.g., forward 2 years:

     $$=EDATE(A1,24)$$

     ).
   • EDATE maintains end-of-month dates (e.g.,

     $$=EDATE("31-Jan-2019",1)$$

     returns February 28, 2019).
   • Leap years are respected (e.g.,

     $$=EDATE("31-Jan-2020",1)$$

     returns February 29, 2020).

Remember, EDATE is versatile for various date-related calculations!

The EDATE function with examples in Excel

The EDATE function in Excel allows you to shift a date by a specified number of months, either into the future or the past. Here’s how it works:

1. Syntax:

   • EDATE(start_date, months)
   • start_date: The initial date (a valid Excel date).
   • months: The number of months to add or subtract.

2. Examples:

   • To find the date 6 months in the future from January 1, 2023, use:

     $$=EDATE("2023-01-01",6)$$

     (result: July 1, 2023).
   • For a date 4 months ago from today, use:

     $$=EDATE(TODAY(),-4)$$

     (result: 4 months before the current date).

3. Additional Scenarios:

   • To move by years, multiply the number of years by 12 (e.g., forward 2 years:

     $$=EDATE(A1,24)$$

     ).
   • EDATE maintains end-of-month dates (e.g.,

     $$=EDATE("31-Jan-2019",1)$$

     returns February 28, 2019).
   • Leap years are respected (e.g.,

     $$=EDATE("31-Jan-2020",1)$$

     returns February 29, 2020).

Remember, EDATE is versatile for various date-related calculations!

The DSUM function with examples in Excel

The DSUM function in Excel is a powerful tool for calculating the sum of specified fields based on specific criteria. Let’s dive into how it works and explore some examples:

1. Syntax:

   • The DSUM function has the following syntax:

     DSUM(database, field, criteria)
     

   • Here’s what each argument means:
     • database: The range of cells that hold all the entries (your data).
     • field: Indicates the column (field) to be calculated for the sum.
     • criteria: The range of cells where specific conditions are assigned.

2. Criteria Types:

   • DSUM allows various criteria types to filter data from the range. Some commonly used criteria include:
     • "Unit Price": Rows matching the string “Unit Price.”
     • Cook*: Rows starting with “Cook” (wildcard).
     • *ies: Rows ending with “ies” (wildcard).
     • 120: Rows equal to the number 120.
     • >120: Rows greater than 120.
     • <120: Rows less than 120.
     • >=120: Rows greater than or equal to 120.
     • <>120: Rows not equal to 120.
     • =B7: Rows equal to the value in cell B7 (formula).

3. Example 1: Summing Unit Price:

   • Suppose you want to calculate the sum of the “Unit Price” field. Use this formula:

     =DSUM(B8:H19, "Unit Price", B5:C6)
     

     • In this formula:
       • B8:H19 is the range.
       • "Unit Price" is the specified field.
       • B5:C6 contains specific criteria (e.g., Order IDs greater than 10021 or Quantity sold ≥ 120).
     • The DSUM function evaluates to $3.74 by summing favorable entries ($1.87 + $1.87).

4. Example 2: Total Price Calculation:

   • Let’s calculate the total price of every sold product from the dataset:

     =DSUM(B8:H19, "Total Price", B5:C6)
     

     • Here, we sum the “Total Price” field based on the same criteria.
     • The result will be the total sum value.

Remember, DSUM is a versatile function that adapts to different criteria, making it useful for various scenarios in Excel!

The DOLLAR function with examples in Excel

 The DOLLAR function in Excel converts a number to text using the currency number format. Here’s how it works:

1. Syntax:

   • DOLLAR(number, decimals)
   • number: The numeric value you want to convert.
   • decimals: The number of digits to display to the right of the decimal point (default is 2).

2. Examples:

   • To format the number 12345 as currency with 2 decimal places and the dollar sign ($), use:

     $$=DOLLAR(12345,2)$$

     (result: $12,345.00).
   • For the value 9876.54 displayed as currency with 1 decimal place and the euro symbol (€), use:

     $$=DOLLAR(9876.54,1)$$

     (result: €9,876.50).

Remember that the DOLLAR function returns text, not a numeric value, so it’s useful for formatting purposes but not for calculations.

The DMIN function with examples in Excel

The DMIN function in Microsoft Excel allows you to find the smallest number in a field (column) of records in a list or database that matches specific conditions. Let’s break down how it works:

1. Syntax:

   • DMIN(database, field, criteria)
     • database: The range of cells that make up the list or database. This represents your dataset.
     • field: Indicates which column you want to analyze. You can use either the column label enclosed in double quotation marks (e.g., “Age” or “Yield”) or a numeric position (1 for the first column, 2 for the second, and so on).
     • criteria: The range of cells containing the conditions you specify. It should include at least one column label and at least one cell below the column label where you define a condition.

2. Example:

   • Let’s say we have the following data:
   Table

   Tree	Height	Age	Yield	Profit
   Apple	18	20	14	$105
   Pear	12	12	10	$96
   Cherry	13	14	9	$105
   Apple	14	15	10	$75
   Pear	9	8	8	$77
   Apple	8	9	6	$45

   • To find the minimum profit of either apple trees between 10 feet and 16 feet in height or any pear tree, use this formula:
     • =DMIN(A5:E11, "Profit", A1:F3)
     • Result: $75

Remember:

• The criteria range can be located anywhere on the worksheet, but avoid placing it below the list.
• If the criteria range overlaps the list, it may cause issues.
• To operate on an entire column, enter a blank line below the column labels in the criteria range.

Feel free to try this out in your Excel worksheet!

The DGET function with examples in Excel

The DGET function in Microsoft Excel is a powerful tool for extracting specific data from a large dataset based on specified criteria. Let’s dive into how it works and explore some examples:

1. Syntax:

   • DGET(database, field, criteria)
     • database: The range of cells that make up the list or database.
     • field: Indicates which column you want to retrieve data from (use column labels or numeric positions).
     • criteria: The range of cells containing the conditions you specify.

2. Example 1: Using DGET with One Criterion:

   • Suppose we have a dataset with cloth items, sizes, colors, and prices. We want to find the price of the item “Shirt.”
   • Formula: =DGET($B$4:$E$9, "Price", $B$11:$B$12)
   • Result: The price of the “Shirt” item.

3. Example 2: Using DGET with OR Criteria:

   • If you’re unsure of the product name but know it’s either “T-Shirt” or “Polo T-Shirt,” you can search for both simultaneously.
   • Formula: =DGET($B$4:$E$9, "Price", $B$11:$B$13)
   • Result: The price of the matched item (“T-Shirt” in this case).

4. Example 3: Using DGET with Multiple Criteria:

   • Modify the dataset to have two “Shirt” items with different criteria (e.g., colors).
   • Search for the blue color among the “Shirt” items.
   • Formula: =DGET($B$4:$E$10, "Price", $B$12:$C$13)
   • Result: The price of the blue “Shirt.”

Remember:

• If no record matches the criteria, DGET returns the #VALUE! error value.
• If more than one record matches the criteria, it returns the #NUM! error value.

Feel free to try these examples in your Excel worksheet!

The DELTA function with examples in Excel

The DELTA function in Microsoft Excel is a handy tool for testing whether two numeric values are equal. Here’s how it works:

• Syntax: DELTA(number1, [number2])

  • number1: The first number you want to compare.
  • number2 (optional): The second number. If omitted, number2 is assumed to be zero.

• When number1 equals number2, DELTA returns 1. Otherwise, it returns 0. This makes it useful for counting pairs of equal numbers.

Here are some examples:

1. To check if 5 equals 4: =DELTA(5, 4) ➡️ Result: 0
2. To check if 5 equals 5: =DELTA(5, 5) ➡️ Result: 1
3. To check if 0.5 equals 0: =DELTA(0.5, 0) ➡️ Result: 0

Remember:

• If number1 is nonnumeric, DELTA returns the #VALUE! error value.
• If number2 is nonnumeric, DELTA also returns the #VALUE! error value.

Feel free to use this function to compare values in your Excel worksheets!

The DEC2HEX function with examples in Excel

The DEC2HEX function in Microsoft Excel converts a decimal number to its hexadecimal equivalent. Here’s how it works:

• Syntax: DEC2HEX(number, [places])

  • number: The decimal integer you want to convert. If the number is negative, the places argument is ignored, and DEC2HEX returns a 10-character (40-bit) hexadecimal number. The most significant bit represents the sign, and the remaining 39 bits represent magnitude (using two’s-complement notation for negative numbers).
  • places (optional): The number of characters to use. If omitted, DEC2HEX uses the minimum necessary characters. It’s useful for padding the return value with leading zeros.

• If number is less than -549,755,813,888 or greater than 549,755,813,887, DEC2HEX returns the #NUM! error value.

• If number is non-numeric, DEC2HEX returns the #VALUE! error value.

• If the result of DEC2HEX requires more characters than specified by places, it returns the #NUM! error value. For example, DEC2HEX(64,1) returns an error because the result (40) requires two characters.

• If places is not an integer, its value is truncated. If places is negative, DEC2HEX returns the #NUM! error value.

Here are some examples:

1. To convert decimal 100 to hexadecimal with 4 characters (padded with two leading zeros): =DEC2HEX(100, 4) ➡️ Result: 0064
2. To convert decimal -54: =DEC2HEX(-54) ➡️ Result: FFFFFFFCA
3. To convert decimal 28: =DEC2HEX(28) ➡️ Result: 1C
4. Attempting to convert decimal 64 with only 1 character place: =DEC2HEX(64,1) ➡️ Result: #NUM!

Feel free to try it out in your Excel worksheet!