The MIN and MAX functions are useful as for returning the first and last dates in a range. They have conditional versions as well MINIFS and MAXIFS which allow you to find the earliest and latest dates based on a condition.
Sometimes you like to point to a cell from the cell on the left using something like ===>. Here is an easy way to automate the length of the arrow based on the width of the cell.
One issue you may find with dates when extracting from a look up table is that an empty date field generates a zero. You can avoid that with a simple formula adjustment.
Excel has a couple of functions that will generate a random number. But the problem is they are volatile. The number changes when Excel calculates. Here’s a technique to generate a random number that doesn’t change.
When you protect a sheet in Excel there is a setting to stop users from selecting locked cells. This means when the sheet is protected, they can’t use the keyboard or mouse to select a locked cell. But you can still select a locked cell in a protected sheet – see how.
I no longer teach the SUMIF function. I teach the SUMIFS function as it provides more solutions because it handles multiple criteria. The two functions differ in their argument sequence which can be confusing when switching between them. Rather than learning both, it is easier to learn the SUMIFS function. There is however one time when the SUMIF function is shorter and easier to use than SUMIFS.
The SUMIFS function is a conditional sum function that allows you to handle multiple conditions. Here is a technique to handle the situation where a condition maybe blank.
If you have ever sorted codes like A1 and A100 you have found that Excel’s standard sort doesn’t usually provide the required sort sequence. Here’s a solution.
When you link to an empty cell Excel it displays as zero. The same happens when you filter a list using the FILTER function. Blank cells display as zeroes. Here’s how to stop that.
In the previous post I showed how to use XLOOKUP to do a two-column lookup. See the link below for the previous post. This time we will see how to do the two column lookup using Power Query.
