Posts

About This Shortcut This shortcut gets help for a selected control on the ribbon.

The best way to solve this problem depends on which version of Excel you use. In the current version of Excel, there is a better way. This new approach is simple and elegant, and it greatly simplifies many complicated formulas. Dont forget to download the worksheet and try it out yourself. The goal The goal is to retrieve information related to the maximum price in the worksheet shown. If the data changes, the formula should automatically recalculate and display updated information....

Explanation With TRUNC, no rounding takes place. TheTRUNC functionsimply slices off the decimal part of the number with default configs. What about INT or ROUND? You might wonder if you’re able to useINT function, or theROUND functioninstead. However, for negative numbers, the rounding that INT does is a bit strange. This is because INT rounds negative numbers down away from zero, no matter what the decimal value. See the last 2 examples in the screen above for an example....

Although EOMONTH is a more convenient function, it won’t accept a range of dates in anarray formula. In that case, you could use the alternative above.

The result is an array like this: where TRUE represents dates in the same month and year. The number 1 is then divided by this array. The result is an array of either 1’s or divide by zero errors (#DIV/0! ): which goes into LOOKUP as the lookup array. LOOKUP assumes data is sorted in ascending order and always does an approximate match. Finally, LOOKUP returns the corresponding value in result_vector, which contains the amounts in C5:C13....

Explanation This formula takes advantage of the fact that TRIM will remove any number of leading spaces. We look for line breaks and “flood” the text with spaces where we find one. Then we come back and grab text from the right. Next, the RIGHT function extracts 200 characters, starting from the right. Note: 200 is an arbitrary number that represents the longest line you expect to find in a cell....

The data is sorted by date in ascending order. The goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. The challenge is that Excel lookup formulas will return the first match by default. There are several ways to approach this problem. In the current version of Excel, a good option is the XLOOKUP function. Another more flexible approach is to use the FILTER function with the TAKE function....

In older versions of Excel, you’re free to use an alternative formula, as explained below. It returns TRUE if the value is numeric, and FALSE if not. TheSEARCH functionis used to find the starting position of a specific substring within a string. The syntax is SEARCH(find_text, within_text, [start_num]). If the substring (find_text) is found, SEARCH returns the starting position as a number. If not, it results in an error. TheXLOOKUP functionis a modern upgrade to the older VLOOKUP function....

The easiest way to do this is with the newer TEXTAFTER function. Both options are explained below. Note the contrast between the modern solution and the legacy solution. TEXTAFTER extracts text that occursaftera given delimiter. In its simplest form, TEXTAFTER only requires two arguments, text and delimiter. Normally, a positive instance number tells TEXTAFTER to count instances from the left. Anegativeinstance number tells TEXTAFTER to count instances from theright. In other words, we are asking TEXTAFTER for the text after thelastspace....

In the current version of Excel, the easiest solution is to use the TEXTBEFORE function. Both approaches are explained below. TEXTBEFORE extracts text that occursbeforea given delimiter. In this configuration, the TEXTBEFORE function simply returns all text that occursbeforethe comma. TEXTBEFORE has many other options that you’re able to read . This is done with theFIND functionlike this: FIND returns the location of text as a numeric position. Because the comma appears as the 6th character in the text, the FIND function returns 6....

For example, to get the last Thursday of a month, set dow to 5. Note: I ran into this formula in ananswer on the MrExcel forum by Barry Houdini. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. it’s possible for you to use the WEEKDAY function inside other formulas to check the day of week.

Keep in mind that there will be 100 spaces between each word. Next, the RIGHT function extracts 100 characters, starting from the right. If you have some odd situation with super long words, bump this number up as needed. Handling inconsistent spacing If the text you are working with has inconsistent spacing (i.e. extra spaces between words, extra leading or trailing spaces, etc.) This formula won’t work correctly. SUBSTITUTE Function The Excel SUBSTITUTE function replaces text in a given string by matching....

To this result, we add 1, which results in the first day of the next month. The WORKDAY function automatically steps back 1 day, taking into account any weekends. The result is the last workday of the month. If you should probably customize weekend days, you might use theWEEKDAY.INTLfunction. By default, WEEKDAY returns 1 for Sunday and 7 for Saturday, but this is configurable. you could use the WEEKDAY function inside other formulas to check the day of week....