Using formulas to repeat list, I call it LoopList
Used to list Months for several years, in away that with 1 formula, it will loop through all months, then jumps into next year and loop through its months again, and so on
Category: Formulas – combined functions
Two or more functions = One Formula, how to combine more than one function to present a powerful formula that does the job well
Scorring, saving multiple outputs in 1 number
I have seen this practice before several locations
Returning multiple outputs in 1 single number.
for example
Compare2, a tool to compare two sheets using formulas
Tool will compare between two sheets using formula/functions method. Once you type in full folder locations (for both files), file names and sheets names in file1 and file2. Make sure these two files are open, then sheet will be updated with comparison results, showing that every cell is checking for its equivalent addresses from both…
Read More “Compare2, a tool to compare two sheets using formulas” »
Skills Grid
A small Excel file to show how we can create a chart-like Excel sheet
Used in my Resume to show different skill sets and the level of expertise in each
IFS (and IFNA) to avoid nested IFs
I tend to see more and more the usage of nested IF functions recently
Now, do not get me wrong, IF is great, but come on, are you going to use it for more than 2 conditions? seriously?
Excel 2013 comes with IFS, the perfect alternative to nested IFs
As you can see, takes up to 127 conditions, neat, right?
Limited Ceiling
The function CEILING (CEILING.Math as of Excel 2016) is a very good powerful function.
It allows you to round number to any value up
Unlike ROUND, ROUNDUP and ROUNDDOWN, that are limited to number of decimal places.
CEILING can round to any number, decimal or not.
Below is an example of rounding the number in D2 which is 22 into the next 7 up, 7 is in D3, result is 28 as you can see
Count cells with condition – multiple conditions
We did see who we can get total of a column for a certain month in certain year using SUMIFS.
SUMIFS got a sister function, COUNTIFS
Which will count how many times cells found with more than 1 condition
So, when you have a table like this…
Read More “Count cells with condition – multiple conditions” »
IF with AND and IF with OR
The powerful function If can already do a lot of tricks, but we can for sure do more when we use it with AND or OR functions.
Also, once we understand that, we can use the power of combining AND and OR inside the Logical test of IF, to make it even smarter
Once good example is when we need to see if a certain date is within the same month as this month like below…
OFFSET+MATCH
One of the power of combining functions in one cell is using the best of these functions
An example is here, OFFSET + MATCH will allow you to do something similar to VLOOKUP.
Why do we want to do that?
VLOOKUP will search in the left-most column in a table to get a value from a column on the right. But this is not the situation all the time
We needed several times to locate a row by using a column on the right, then get the value from a column left to it.
This is when VLOOKUP can not help us.
Count how many times letter found
Found this in an old file while doing some digital cleaning This will show you how you can easily count how many times a letter (or any string) into a cell. So the main formula doing that is … =LEN(I12)-LEN(SUBSTITUTE(I12,”o”,””)) Of course, this assumes you are looking to find how many the letter “O” is…
Multiple lines in cell using functions
You must already know that when you press Enter after you modify a cell, the cell content will be saved and you jump down to cell below (we can change that behavior by the way, but that is another post).
And you could have already know that if you want to have multiple lines inside a cell, you need to press ALT+ENTER to do that.
But, how can we do that in formula ??
I got a request on how to do that in a spreadsheet, so, the client wanted to convert two columns having two parts of address, Column C has Street address and street name, and column D has city, state and Zip code.
