What does it do?
In brief summary, its a test of one number vs another, in it’s basic for this test will output a TRUE or FALSE answer. This basic function can be used to integrate into other Excel function such as SUMIF and IF functions to name but two, 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
Before we look at several examples let’s look at the above in one basic version.
How To Use Excel Less Than Or Equal To Function ? Above the 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.
How To Use Excel Less Than Or Equal To Function ? 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.
When using a test of number 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.
How To Use Excel Less Than Or Equal To Function ? There may be cases where some cross referencing of figures is fractionally out. I’ve personally seen this on reports where it’s 1 millionth of a fraction out between two separately calculated figures. This is easily fixable by applying 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.
How To Use Excel Less Than Or Equal To Function ROUNDING in the formula
ROUND function is very simple = ROUND(reference, number of decimal places to round to). ROUND on its own will ROUND DOWN number 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 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.
How To Use Excel Less Than Or Equal To Function Using <= in an IF function
Scenario: You want to analyse sales performance over the year. We can do this by adding a column to analyse the data, see below.
Column F looks at the TARGET column and asks is it LESS THAN OR EQUAL to the ACTUAL. Since this is a basic query it will output 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.
How To Use Excel Less Than Or Equal To Function ?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 of to achieve similar results and they can be used to trigger literally tens of function. There are so many to list that this article could be a number of volumes.Instead of displaying a message for TRUE of FALSE, imagine instead 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 1 months warning and THEN populates a total.
How To Use Excel Less Than Or Equal To Function Using <= in a SUMIF
Same scenario above but all months’ targets are £850, this has now been inserted into an 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 further develop your spreadsheets.
Just remember to enclose your symbol into quotation marks as they are text when using them inside the function.