You don’t have to use an IF function to get the most out of logic calculations in Excel.

Take the case where you want to determine the Australian financial year (which starts in July) from a date.

If A1 contains a date then a common solution would be an IF function similar to the one below.

=IF(MONTH(A1)>6,YEAR(A1)+1,YEAR(A1))

Logic Calculations

A logic calculation involves using the result of a logic test. A logic test is usually the first part of an IF function and in the above example the logic test is

MONTH(A1)>6

This returns either TRUE or FALSE (all logic tests must return TRUE or FALSE). In Excel TRUE equals one and FALSE equals zero. This allows us to the use the logic test results in calculations. When you add or multiply by a logic test you convert it into the number.

We can rewrite the above formula so that we don’t need the IF function.

The alternative formula is

=YEAR(A1)+(MONTH(A1)>6)

The logic test (MONTH(A1)>6) will return TRUE when the month number of the date is above six and since TRUE equals one, then one will be added to the year of the date in cell A1.

When the month number of the date is six or below then the logic test will return FALSE or zero, so zero will be added to the year of the date.

In general you always enclose the logic test within brackets to ensure it calculates correctly.

You can’t convert all IF functions like this but if the calculations involve a one or a zero it is possible.

Logic Test Multiplication

Remember that when you multiply any number by zero you will zero the number. If you have a situation where you either use a number or you use zero, then you can multiply by a logic test to achieve the same result.

Take a GST example where you either charge GST or you don’t based on some criteria. To calculate the GST you might use a formula like this

=IF(D2="Export",0,C2*10%)

This can be converted to

=(D2<>"Export")*C2*10%

Note: the alternative formula uses <> in the logic test. This means not equal to, so D2 is not equal to Export.
The result of the logic test will either be TRUE (1) or FALSE (0). Multiplying by TRUE leaves the calculation unchanged and it will calculate GST. A FALSE result will zero the GST amount.
(Note: In Australia GST is not charged on exports)