Using the DATEDIF function DATEDIF is a function that works out the age of the employee to display the year, month, and day in the cell. When working with the DATEDIF function, we will need to type the function into the cell to construct it from scratch. The DATEDIF function is not resident in the […]
Using the DATEDIF function Read More »
Date-Time Functions and Enhancing PivotTable Dashboards In this chapter, we will explore date functions and look at how to work with time. Here, we concentrate on DATEDIF(), YEARFRAC(), EDATE(), WORKDAY(), and many more functions to be more productive in the workplace. In addition, a large part of this chapter will explore a host of
Date-Time Functions and Enhancing PivotTable Dashboards Read More »
Using the COUNTIFS statistical function In our previous edition book, Learn Microsoft Office 2019, we learned all about the COUNTIF function. Let’s look at an example to recap this function, and explore the COUNTIFS function at the same time. Recapping the COUNTIF function In the next topic, we will expand on prior knowledge and discover
Using the COUNTIFS statistical function Read More »
Constructing DAVERAGE We will now work out the average gross margin: Figure 10.28 – Formula construction for the average gross margin above 50% See whether you can work out the maximum total cost for Division 2, the total quantity sold for Division 1, and the minimum quantity sold for Division 3, using the appropriate function
Constructing DAVERAGE Read More »
Exploring database functions There are twelve database functions in Excel that cover most of the clusters of functions. These functions have been in the Excel library for many years, and are often forgotten about or not known, as there are so many other functions we can use today. Database functions are a substitute for IFS
Exploring database functions Read More »
Investigating the LET function The LET function is used to simplify formulas. It is a method to get Excel to compute the same expression used within a formula by naming it, leading to more efficient processing, and making the formula easier to understand as it is less complex in construction. Let’s look at the
Investigating the LET function Read More »
Exploring XMATCH The XMATCH function will return the relative position of an item within a range or an array. We will firstly look at a basic XMATCH construction to return a result and then look at an example that includes XMATCH and INDEX together. Constructing XMATCH (syntax) The syntax for XMATCH is similar to
VLOOKUP AND MATCH The VLOOKUPMATCH worksheet consists of a Vet Service Report table listing services provided by each salesperson in cells A6:F13. Instead of taking the time to run along rows, columns, and salespersons, we would like to choose the service provided for a particular salesperson and return the value for that service. In the
Building on VLOOKUP Before we discover new LOOKUP functions in Office 2021, we will build on our existing VLOOKUP skills cultured from our previous edition book, Learn Microsoft Office 2019. It is important to understand how different functions work together or to find fewer complex methods to generate the desired output. Combining IFERROR AND VLOOKUP
Building on VLOOKUP Read More »
Building IFS functions The IFS function was first introduced in Office 2016, so it is not a new function. It is so much easier to construct and evaluate multiple IF conditions instead of using the old clunky nested IF formula. The IFS syntax is extremely simple: =IFS(logical_test1,value_if_ true1,[logical_test2,value_if_true2], and so on up to 127 conditions.
Building IFS functions Read More »
