Most people are unaware that the SUMIFS function has a serious limitation when it comes to codes with leading zeroes. This post shows you how to perform calculations involving codes with leading zeroes. This issue also affects SUMIF, COUNTIF and COUNTIFS.
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
I was looking at a calendar and noticed it used alternately shaded cells, like a checkerboard, for all the dates and thought Excel could do that.
Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.
Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
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.