I have seen this practice before several locations
Returning multiple outputs in 1 single number.
for example
Category: Logical
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” »
BMI Calculator
A simple calculator, how come we do not have one like this?
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
Text-to-Columns, dynamically using formulas
I often use the technique of concatenating columns into 1 cell with separators.
Something like the CSV, 1 line that has all values for a single row (all columns for that row) into 1 text block.
And then, because of that, I need to extract that back, into table
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?
Highlight dates dynamically
Conditional formatting is another powerful feature in Excel, especially when you combine it with functions
A simple function as in below if you set it up inside Conditional formatting, can do magic
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…
New function IFERROR, finally
We have already gotten used to use VLOOKUP, MATCH and other functions that generates errors inside an IF
But, we do not need that anymore
We can use the new guy, IFERROR instead, which will show the result if it did not generate error, otherwise will show something else if it resulted an error.
Calculate hours between two cells
We needed that several times in the past Then I found it in an old file in my laptop, thought to share it for others to help So, when A1 has the start time, B1 has the end time Time different in one day will be from formula =IF(OR(A1=””,B1=””),””,ABS((HOUR(A1)+MINUTE(A1)/60)-(HOUR(B1)+MINUTE(B1)/60))&” hrs”) I added to prevent…
Workdays – Across months
Get total number of days per month
This was a question from one of my friends, This set of formulas will calculate how many days (Networkdays) in each of the months in the list for a given Start and End dates
If Range of dates are across multiple years, user can copy columns C, D, E and F to fill those years
The months for the list are automatically populated starting from Jan of the year of the start date
The main formula in F7 should be: