When receiving a list of data, it can be desirable to find unique values. This can be accomplished in Excel using the UNIQUE() function, or by using other methods for getting unique values. But, what happens if you’d like a …
Category: formulas
BYROW() in Excel: A Marginal Tax Schedule Example
The BYROW() function allows calculations to be done on a row by row basis. This allows for new functions to be created that are similar to the SUMPRODUCT() function, but are much more customizable. In this example, we will be …
Unique Values from 2 Lists in Excel – Multiple Methods
There are many times where you will need to acquire a unique value from not just one list of values, but from two separate lists. Getting a unique value from a single list is straightforward, and can be accomplished in …
LAMBDA() in Excel – Generating an Amortization Schedule
Use the LAMBDA() function to create a loan amortization schedule!
LET Function in Excel: A Name Parsing Example
The LET function is one of the most exciting formulas to be added to Excel in 2020. This formula allows a spreadsheet maker the ability to create names for calculations (within a cell). These names not only make your formulas …
Top N Results from Excel using Dynamic Arrays
Getting the Top N results from a list of data, such as a Top 10 list is a very common Excel request. While this was possible before dynamic arrays, it has been made much easier using dynamic arrays. In this …
#SPILL! error – What causes it to occur?
With the introduction of dynamic arrays comes a new type of error; the spill error. Other errors, such as #N/A, #NUM! and #REF! have existed in Excel for many years. In many cases, workarounds for these errors have been created …
3 different ways to get unique values in Excel
It is very common to require a list of unique values from a list that contains duplicates. While it may be easiest to take a single approach to get these values, there are cases where a different approach may be …
Using XLOOKUP and TRANSPOSE
With XLOOKUP , it is now possible to return multiple values from a single function. However; when these values are returned, they show in multiple columns. If we want to have them appear by row, we have to use the …
XLOOKUP – New abilities
In my last post, I detailed how to replace existing VLOOKUP and other lookup formulas with XLOOKUP. However; XLOOKUP is more than just a replacement to the VLOOKUP and INDEX / MATCH functions! XLOOKUP adds some additional functionality that was …