6 new Excel functions that make your work more efficient

6 new Excel functions that make your work more efficient


With 6 new Excel functions including: TEXTJOIN, CONCAT, IFS, SWITCH … help you to simplify some general calculations and help you avoid tedious work in building custom functions to complete the work. this calculation. Here are 6 new Excel functions that make your work more efficient


Functions TEXTJOIN and CONCAT to combine a list or range of text strings or functions MAXIFS and MINIFS to calculate the maximum and minimum values ​​in the region for one or more conditions and functions IFS and SWITCH Minimize clutter when using nested IF functions. This is 6 new Excel functions To help work more effectively, please follow the article below to learn how to use these new Excel functions.

6 New Excel functions make your work more efficient

Combine text strings using the TEXTJOIN and CONCAT functions

Combining text strings is a common task for spreadsheet users. However, until now, if you want to combine text strings from the range reference of cells, you have to specify individual cells, so with the Excel trick using the TEXTJOIN and CONCAT functions, you will do makes combining strings simpler.

Jaw TEXTJOIN and jaw CONCAT New lets you combine text strings from range references of cells with or without using delimiters, such as commas to separate each item. You only need to reference the area and specify the separator once, and Excel will do the note for you.

Traditional solution:

= CONCATENATE (A3, “,”, B3, “,”, C3, “,”, D3, “,”, E3)

The new solution uses the TEXTJOIN function to concatenate text strings:

= TEXTJOIN (“,“, TRUE, A3: E3)

Let’s say you only want to combine parts of an address into a single text string. Using a traditional solution would require you to specify each cell and repeat a comma to separate each part:

The new solution is much simpler. You just need to specify the comma (or whatever separator you want), choose whether to ignore blank cells or not, and then specify the area.

New Excel 6 hams your job better than 2

If you want to learn more about TEXTJOIN and CONCAT functions, you can find more information about excel tips and examples of functions on the Internet.

The IFS function and the SWITCH function help define a variety of conditions

Functions IFS and SWITCH New gives the user alternatives to using a variety of nested IF functions, for example the “IF (IF (IF ())” statement, in case there is more than one condition the user want to check to find a corresponding result.

IF function is one of the most commonly used new Excel functions in Excel, and using the IF function inside the IF function (nested IF function) is also quite common in Excel, but can be confusing.

The advantage of using the new IFS function is that you can specify a range of conditions within a single function. Each condition followed by the result will be used if the condition is True. For example, let’s say you want to score a letter scale for a given score on a test. Using the IFS function, the formula would be:

= IFS (C1> = 90, “A”, C1> = 80, “B”, C1> = 70, “C”, C1> = 60, “D”, C160,>

New Excel 6 hams your job better than 3

In the above formula it can be understood that if the score in C1 is greater than or equal to 90, it is equivalent to point A. And if the score is greater than or equal to 80, it is B. If the score is greater than or equal to 70, it is point C.

SWITCH function also handles many conditions. The SWITCH function’s difference is that instead of specifying a series of conditional statements, you specify an expression and a range of values ​​and results.

The values ​​are compared against the expression, and when the first exact match is found, the corresponding result is applied to the cell. You can also specify a “default” result to be returned if there is no value that exactly matches the expression. Advantages of SWITCH function That is, you can avoid repeating the expression over and over, which happens when using nested IF formulas.

In the example below, the first part of the formula will extract the size code (i.e., XS, M, and G) from the middle of the item in column B. It is quite long so when using the SWITCH function is reasonable, just write one-off recipe.

An example can be explained as follows:

Extract the size code from the item in column B. If it equals “XS”, the result is “Extra Small”. If it equals “S”, the result is “Small”. If there is no result, the result is “Not Specified (not specified)”.

New Excel 6 hams your job better than 4

The same result can be calculated using the nested IF function, but the formula will be significantly longer, as shown below:

New Excel 6 hams your job better than 5

Use the MAXIFS and MINIFS functions to filter and calculate data

If you are familiar with the COUNTIFS, SUMIF, and AVERAGE functions, there is no need to explain the functions much MAXIFS and MINIFS. The basic MAX and MIN functions calculate the maximum or minimum value in an area, while the AVERAGE function supports the average, the SUMIF function conditionally calculates the sum, but what if you need to apply it Conditions to filter my data? This is exactly what the MAXIFS and MINIFS functions allow.

You can specify one or more data filtering conditions before calculating the maximum or minimum value. Conditions may be applied to neighborhoods or ranges containing values. For example, let’s say a retailer has a table that contains sales data for all of their stores. They can use the MAXIFS and MINIFS functions to calculate the maximum and minimum sales for a specified item in stores that are located in a certain region.

In the example below, the MINIFS function and the MAXIFS function are used to calculate the minimum sales and maximum sales figures from the table, but include the values ​​from the Sales column only if the value in the Retailer column is “BigMart. “, the value in the Brand column is” Longlast “, and the value in the Sales column is greater than 0.

New Excel 6 hams your job better than 6

Above are 6 new Excel functions to help work more efficiently, they can be applied in many different fields. So, it can be seen that Excel’s support is immense and great.

https://thuthuat.taimienphi.vn/6-ham-excel-moi-giup-cong-viec-cua-ban-hieu-qua-hon-23812n.aspx

.

Related Posts

Leave a Reply

Your email address will not be published.