A key feature of Excel is that you can use different formulas to achieve specific results and order the data in the worksheet. To make formulas, you need to be able to use operators.
There are four types of operators: arithmetic operators, comparison operators, text concatenation operators, and reference operators. The standard symbols for MORE THAN, LESS THAN, and EQUAL are precisely comparison operators. They can also be combined to check whether one value is less than or equal to one or more than or equal to another. The IF function is often used for this purpose.
So let’s take a closer look at how you can use these operators in Excel.
Content
How to use LESS THAN OR EQUAL in Excel
Sometimes users may encounter uncertainty when making calculations in Microsoft Excel. This means that the result in the table will depend on certain events. This may be the raw data or the results of calculations in other formulas. LESS THAN OR EQUAL (<=) is rarely used alone and is often combined with other Excel functions, such as IF, OR, NOT, SUMIF, COUNTIF, etc., to perform powerful calculations.
Suppose we have some values and need to assign a quality attribute to each. So we have Items, Sales, and Status.
Let’s set up the formula as follows, for example. If the sales are less than or equal to 40, then the Status column will show Enough. If there are more than 40 sales, the Status column will show Order More.
- Click on the cell (it’s C2 in my example) and enter the following command:
- =IF(B2<=40;”Enough”;”Order More”)
- After that, you can use the mini black square in the bottom right corner of the selected cell to apply this formula for all Status cells.
- Here’s the result.
In this case, I have specified B2 so that the formula will consider the value in this particular cell. You can change it depending on which cell the data is in your spreadsheet. You can do the same not only with numbers but also with words. For example, you can make a formula where a positive value will only be output if the words in both cells are the same, or in Excel parlance, equal to each other.
As you can see, this formula works and has been applied to all values. This is a simple way to use the LESS THAN OR EQUAL operator. Let’s look at a more complex comparison below.
How to make a more complex LESS THAN OR EQUAL formula in Excel
Often Excel users have to work with more complex data sets and make more complex calculations. For example, let’s look at how to predict the balance status in a warehouse.
To do this, you need to know the average sales over a certain period and the current balance. As an example, I will predict two weeks.
To make it work, I need to subtract from the Balance for today what will be sold in two weeks. So let’s use the following formula.
- =C2-2*B2
- Then apply this formula to the column, as shown above. And here’s what we will have in two weeks.
However, as you know, there can be no negative balance in a shop or warehouse. If there is no product, then the value must be 0. Therefore, let’s modernize our formula and add a couple of conditions.
- =IF(C2-2*B2<0;0;C2-2*B2)
- Then apply this formula to the column, as shown above. Here’s the result.
As you can see, the sheet shows 0 in the cells where the item has run out. This way, you can easily calculate how much is left and how much more you need to order.
How to use LESS THAN OR EQUAL with SUMIF in Excel
The SUMIF function calculates the sum of a range of cells when the corresponding cells fulfill a specific condition. Here’s what it looks like in general:
- =SUMIF(range,criteria,[sum_range])
Suppose you need to calculate the sum of all sales over a certain period. So you can use <= to specify up to which date to do the calculation. For example:
- =SUMIF(B2:B7,”<=31-Dec-2022″,C2:C7)
All sales that occurred on or before 31 December 2022 are summed up now. There can be many such examples. But these were the main ones you need to know to understand the basic principle of using LESS THAN OR EQUAL.
