This basic function can be used to integrate into other Excel functions such as SUMIF and IF functions; we will look at these examples below.
As we’ll be looking at COUNTIF and IF see below, please refer to those articles for a full run down and explanation of the syntax involved;
<= Symbols explained
< on its own means IS LESS THAN
= on its own means IS EQUAL TO
>= combined means IS LESS THAN OR IS EQUAL TO
Examples
Before we look at several examples, let’s look at the above in one basic version.
The above table shows < in Column D, = in Column E and combined <= in Column F. Each formula in these columns is checking if the number found on each row in column B is LESS THAN, EQUAL TO or LESS THAN OR EQUAL TO the number found on the same row in column C.
Above cell, F6 is selected, and the formula is =B6<=C6; if you evaluate the formula manually, you get this =5<=6, so IS 5 LESS THAN OR EQUAL TO 6.
I think we can all conclude that answer is a resounding YES. Excel will translate this YES into a TRUE result.
IMPORTANT
When using a numbers test, it is vitally important for your sanity to note cell formatting, see the example below that uses the same formulas in the first example. Note DATA B and DATA C appear identical, yet there is a mix of TRUE and FALSE’s across each column; why is this, you may wonder?
Further inspection and changing the cell formats to 2 decimal places reveals evidence of cell formatting.
There may be cases where some cross-referencing of figures is fractionally out. I’ve personally seen this on reports where it’s one-millionth of a fraction out between two separately calculated figures. This is easily fixable by applying the ROUNDING function.
This will to do with how the figures were totals and where Excel has applied some rounding along the way. You can either apply the rounding directly into the formula or into your cell with your data (assuming, of course, that data is formulated); the latter isn’t ideal of the figures are static numbers and need to change periodically.
ROUNDING In The Formula
The ROUND function is very simple = ROUND (reference, number of decimal places to round to). ROUND on its own will ROUND DOWN numbers 1 to 4 after the decimal and ROUNDUP 5 to 9.
Note: There is also a ROUNDUP and ROUNDDOWN.
These perform as expected by their function names and use the same criteria method for a number of decimal places to round to.
Above in cell F6, what was just C6 is now ROUND (C6,0), this will round the 4.99 to zero decimal places, and the numbers after the decimal is a .9 it will ROUNDUP to make 5.00. The same result will be met with 5.49, where .4 will ROUNDDOWN to make 5.00. No change occurs with the whole number 5.00 on row 8.
Using <= In An IF Function
Scenario: You want to analyze sales performance over the year. We can do this by adding a column to analyze the data; see below.
Column F looks at the TARGET column and asks it is LESS THAN OR EQUAL to the ACTUAL. Since this is a basic query, it will output a basic TRUE of FALSE results.
We can take this to the next stage by adding an IF function and populating a message to let us know how we performed. There is an article on IF function, so please read that if you need to; in the meantime, I’ll march on.
In the above, our IF function in column H will display TARGET ACHIEVED when a TRUE result is found, and TARGET MISSED when FALSE. For ease of reference, F3 shows the formula that is inserted into the logical_test part of the IF function.
You can use any of the > < = or a combination to achieve similar results, and they can be used to trigger tens of functions literally. There are so many to list that this article could be a number of volumes.
Instead of displaying a message for TRUE or FALSE, imagine instead of using a VLOOKUP.
The example above looks up to the WORKSHEET 2 table when a date has expired on a subscription; otherwise, it just returns zero. This can be used to help trigger alerts of 30, 60 days; for example, see below.
Above the formula has a -30 after D4m; this reduces the expiry date by 30 days to give one month’s warning and THEN populates a total.
Using <= In A SUMIF
The same scenario as above, but all months’ targets are £850; this has now been inserted into a SUMIF function. Below we are looking at the ACTUAL range, and where it is <= LESS THAN OR EQUAL TO 850, we want to add up all those totals.
Note: This will be all the yellow figures PLUS the red cell as 850 is TRUE for <=850. The result is shown in H4.
I hope you have found this article useful. As mentioned above, the same methods apply for < or > or = and a combination of >= and <=. These options will give you some great analysis tools to develop your spreadsheets further.
Just remember to enclose your symbol into quotation marks as they are text when using them inside the function.
