Category: IF

Compare2, a tool to compare two sheets using formulas

December 5, 2018 ANmar

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…

Conditional Formatting, Formulas - combined functions, IF, Logical, Standard functions

BMI Calculator

December 5, 2018 ANmar

A simple calculator, how come we do not have one like this?

Skills Grid

October 26, 2018 ANmar

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

IF with AND and IF with OR

December 25, 2017 ANmar

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…

AND, Formulas - combined functions, IF, Logical, OR, Standard functions

Calculate hours between two cells

February 27, 2017 ANmar

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…

ABS, Basic Math, Date and Time, Hour, IF, Logical, OR, Standard functions

Workdays – Across months

November 3, 2015 ANmar

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:

Read More “Workdays – Across months” »

DATE, Date and Time, Formulas - combined functions, IF, MONTH, NETWORKDAYS, Standard functions, YEAR

Week number to Month number

May 11, 2015 ANmar No Comments

Now we need to convert week number to month number

This is exactly the same as week number to date (reverse weeknum)

So, when the week number is in A5, then formula below will get you the month number

DATE, Date and Time, Formulas - combined functions, IF, Logical, MONTH, TODAY, WEEKDAY, YEAR

iframe in Excel (XLiFrame)

February 19, 2012 ANmar No Comments

This is the iframe in Excel, if you are familiar with the HTML concept of iframe, you will understand this one right away, it is basically the same project as HoScrollArea but with vertical scroll too.

Formulas used to bring actual data is:

=IF(OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN())="","",OFFSET(INDIRECT("'"&$F$2&"'!$A$1"),$D7-1,$D$6-6+COLUMN()))

While formula used to bring header labels:

ADDRESS, COLUMN, Formulas - combined functions, IF, INDIRECT, LEFT, Lookup and References, OFFSET, Standard functions, Texts and Strings

Convert 2-column into wide (column-row) table

February 9, 2012 ANmar No Comments

This Excel file will convert a table of two columns into a Column-Row table
Using functions only and auto-updated once the Main table update

In another word, analyze the table into wider form

I needed this one few days ago and you will need this one too.

The main formula that you need to use is:

Formulas - combined functions, IF, Lookup and References, MATCH, OFFSET, ROW, Standard functions

Multiple-Validations (connected to each other)

February 7, 2012 ANmar No Comments

Here is the free file for a special request by a client
He wanted to have multiple cells having Data – Validation to same list but minus the selected one

In English, once you select one from cell1, cell2 will bring the same list but without the selected one, then when you select another item is selected in cell2, cell3 will bring what left from the list.

Does that make sense, check out the screenshots

Data Validation, Formulas - combined functions, IF, Logical, Standard functions

Insert Blank rows into table using functions

February 5, 2012 ANmar No Comments

Here you will see how to work with functions to insert a blank row every certain number of rows in a table using a blank sheet to copy all values of that table into the new sheet Then using Copy > Paste Special to make them constants The new thing is that this is all…

CHAR, COLUMN, Formulas - combined functions, IF, INDIRECT, INT, Lookup and References, ROW, Standard functions