File renaming applications are great if you need to rename a lot of files. They can be even better if you can use Excel’s built-in functions such as TRIM, SUBSTITUTE, FIND, LEFT, and RIGHT in order to clean up and generate your new file names. Excel VBA File Renamer for Windows is my take on the idea and can be downloaded here: dailyexcel.net Excel VBA File Renamer for Windows dailyexcel.net Excel VBA File Renamer for Windows enables you to …
Tag: IF function
Combining LET with IF
With many complex calculations, especially those involving one or more IF functions (also, the FILTER function and various lookup functions), the same expression will often be repeated multiple times in a formula. This can result in lengthy and hard-to-read formulas and can also cause performance issues, given that the same (often complex) expression can be calculated multiple times. The LET function allows us to assign names to values and/or calculation results, with those names applying in the scope of a …
Calendar
Dynamic Calendar Template for Excel is now available for download: dailyexcel.net calendar dailyexcel.net calendar currently supports 10 scenarios/countries for years from 2024 to 2028: No single workday rule, as well as three additional from-to periods, are available for collective vacation planning. Hours worked in a week, as well as holiday and vacation hours, are automatically calculated in order to support planning of business days. For supported scenarios, holidays are automatically downloaded via connection to the dailyexcel.net site: …
SORT & SORTBY functions
Two related sorting functions, SORT and SORTBY, replicate and extend Excel AutoFilter sorting functionality in formula form. The SORT function can be used to sort the contents of a range or array based on the values in that range or array, while the SORTBY function can be used to sort the contents of a range or array based on the values in the corresponding range or array, as illustrated here: By default, these functions will return multiple values, which will …
Lookup the 2nd, the 3rd, or the nth value
Both the MATCH function and the XLOOKUP function look only for the first (last) available match in the array. This is perfectly reasonable in most cases, as we are expecting to deal with unique identifiers when looking up data. However, sometimes we will have to look up the second, third, fourth, or nth value. Consider the following example: Our table contains a list of contract numbers in column B and contact email addresses in column A. Contract numbers are …
IFS function
We’ve previously covered how we can nest one or more IF functions inside the IF function. This enables us to perform multiple different actions depending on the tested condition, but it can also make the whole formula longer than necessary and hard to read/understand/correct. The IFS function can take the place of multiple nested IF statements and is, in principle, easier to read. The syntax of the IFS function is as follows: = IFS ( logical_test ; value_if_true ; …
IFERROR and IFNA functions
We’ve previously covered how we can nest the ISERROR function, the ISERR function, or the ISNA function inside the IF function in order to check for errors and perform appropriate actions if errors occur. While effective, this can make the whole formula longer than necessary and hard to read/understand/correct. The specialized IFERROR and IFNA functions with simpler syntax can take the place of the IF ISERROR and IF ISNA combinations. The IFERROR function checks whether the cell or formula …
Custom Icons for Conditional Formatting
With conditional formatting, among other things, we can join indication icons to our numbers. While we can rearrange available icons as we wish, the choice of available icons is fairly limited: In particular, while Green Arrow Up and Red Arrow Down are available, we don’t have the often-requested Green Arrow Down and Red Arrow Up. We can expand our choice of icons with the help of the Wingdings fonts, a series of fonts that render characters as a variety …
Conditional calculations
IF functions for cell ranges Using conditional statements, i.e., the IF function, we can test conditions and perform actions if conditions are met. This IF-THEN-ELSE conditional processing is useful when we want to add something new to our rows. However, it is not really appropriate for retrieving data from or about whole ranges of cells, sometimes containing thousands of rows and columns. For example, in order to sum the incentive paid out to “green” team members, we had to …
Conditional statements
IF function basics Conditional statements, conditional expressions, conditional processing, IF-THEN, or IF-THEN-ELSE are all names for a programming concept where conditions are tested and actions are performed if conditions are met. If a certain condition is met, something is calculated. If that certain condition is not met, either nothing happens or something else is calculated. In Excel, when conditions are met, something is TRUE. When conditions are not met, something is FALSE. TRUE and FALSE are logical values. You will …