If you need a formula to identify the last used cell in a column you don’t have to use an array formula. The AGGREGATE function can calculate it for you.
Hyperlinks are a great way to navigate around large spreadsheets. Unfortunately they each take a few clicks to create and can be easily broken. You can use a function to easily create multiple, flexible hyperlinks.
The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.
Here is the problem – you have a list of values which includes sub-totals calculated using SUM functions. You only want add up the values and ignore the SUM function cells.
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
Excel has had a week number function for many versions, but Excel 2013 added a new week number function that complies with the ISO 8601 standard week number.
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process.
Do you remember factorials in maths? That’s where you have, say four numbers, and you want to find out how many four number combinations you can make with those four numbers. In Maths you write it as 4!. The answer is 4 x 3 x 2 x 1 = 24.
If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match.
Let’s say you have a list of values that has a set sequence. In the list which starts in row 2 you want to add up every second entry. So you want to add up the entries in row 3, row 5, row 7 etc going down the sheet. The SUMPRODUCT function to the rescue yet again.
If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this.
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.
Excel doesn’t have a MINIF or a MAXIF function and many advanced users create an array formula to provide that functionality. If you have Excel 2010 or later there is a non-array solution.
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers.
Excel has great charts to help you visual your numbers, but it can also allow you to use flowcharts to help visualise numbers in a different way and help explain relationships between numbers and how they are formulated.
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
Excel’s Filtering feature (formally called AutoFilter) is quick and easy to use, but it doesn’t always provide enough solutions to your filtering needs.
You have a number of options that require the same treatment. What is the easiest way to identify if an entry is one of a list?