15 Important Excel Formulas to Know: Subtraction, Multiplication to Addition

 


Microsoft Excel is still a favorite application that is widely used to process data. To maximize its use, it is important that we know Excel formulas for subtraction, multiplication, addition and more.

Now to help you, this article will discuss various Excel formulas. This allows you to perform calculations and data analysis faster.


We will provide 15 important formulas to know that are important for users to know. But before that, anyone know what an Excel formula is?




Excel interprets formulas as commands to perform calculations using one or more basic mathematical operations - addition, subtraction, multiplication, and division. So we can say Excel formula is a way to calculate values ​​in a single cell or in a range of cells.


Also, keep in mind that formulas are different from functions. Formulas are entered manually by the user, while Functions contain formulas that are predefined in Excel.







12 Important Excel Formulas

Below are 12 formulas that are highly recommended because they are often used.


1. SUM

The SUM function is to add values. You can add explicit numeric values, cell references, ranges, or a mix of the three. Examples are below:


Formula: =SUM(first cell you want to sum:last cell)



To find the total sales amount for each unit, we simply type the function "=SUM(C2:C4)". This automatically adds 300, 385, and 480. The result is stored in C5.


Alternative:


- =C2+C3+C4 (Sum)


- =C2-C3-C4 (subtraction)


- =C2C4 (multiplication)


- =C4/C2 (division)


Practical tip: If you want to quickly sum values ​​in contiguous (unbroken) ranges, go to the next cell and click the Autosum icon on the Home tab. Excel will insert the SUM function to find the total of all previous cells.



2. AVERAGE

The AVERAGE() function focuses on calculating the average of the selected range of cell values. As seen in the example below, to find the average of total sales, simply type "AVERAGE(B2, B3, B3,B4)".



3. COUNT

The COUNT() function counts the total number of cells in a range that contains numbers. It does not include cells, which are blank, and those that store data in a format other than numeric.


As in the example below, counting from C1 to C4, ideally four cells. But since the COUNT function only considers cells with numeric values, the answer is 3 because the cells containing "Total Sales" are omitted here.



4. SUBTOTAL

The SUBTOTAL() function returns the subtotal in the database. Depending on what you want, we can choose the average, count, sum, min, max, min and more. Let's look at the following example.


Calculation of subtotals on cells from A2 to A4. As you can see, the function used is "=SUBTOTAL(1, A2:A4), in the list of subtotals "1" refers to the average. Therefore, the above function will give the average of A2:A4 and the answer is 11, which is stored in C5.



5. MODULUS

The MOD() function returns the remainder when a certain number is divided by the divisor. Now let's look at the example below for a better understanding.


We have divided 10 by 3. The remainder is calculated using the function "=MOD(A2,3)". The result is stored in B2. We can also directly type "=MOD(10,3)" because it will give the same answer.



6. CEILING

The CEILING() function rounds a number up to its nearest multiple of significance. For example, the nearest highest multiple of 5 for 35,316 is 40.


7. FLOOR

In contrast to the Ceiling function, the Floor function rounds down a number to the nearest multiple of significance. The closest example of a multiple of 5 for 35,316 is 35.



12 Important Excel Formulas to Know: Subtraction, Multiplication to Addition Photo: undefined

8. AND, OR, NOT

The AND formula can be used to find out whether the contents of a cell are true (TRUE) or false (FALSE). Here's the formula:


=AND(logical1, [logical2], ...)


The OR formula is similar to AND. The difference is that the OR formula can be met by only one of the criteria: The formula is as follows:


=OR(logical1, [logical2], ...)


While the NOT formula is the opposite of the AND and OR formulas. The formula is as follows:


=NOT(logical)


9. MAX, MIN

The MAX formula is used to display the largest data value. This formula is fairly simple.


Formula:

=MAX(first value to display:last value to display)


MIN is the opposite of MAX. The MIN formula is used to display the smallest value in the data.


Formula:

=MIN(first value to display:last value to display)


10. LEFT, RIGHT, MID

The LEFT() function returns the number of characters from the beginning of the text string. Meanwhile, the MID() function returns the character from the middle of the text string, with the starting position and length. Finally, the RIGHT() function returns the number of characters from the end of the text string.


In the example below, we use the Left function to get the leftmost word in the sentence in cell A5.



11. UPPER, LOWER, PROPER

The UPPER() function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to uppercase, i.e., the first letter of each word is capitalized, and the other letters are lowercase.


Our example converts text in A6 to full caps in A7.



12. VLOOKUP

The VLOOKUP formula is used to find things in a table or range by row (vertical). For example, looking up the price of an automotive part by its part number, or looking up an employee's name by their employee ID.


Formula:

=VLOOKUP(what to search for, where to look for, column number in the range containing the values ​​to return, returns a near or exact result indicated as 1/TRUE, or 0/FALSE).


13. HLOOKUP

HLOOKUP

The HLOOKUP formula is used to look up a value in the top row of a table or array of values, and then returns the value in the same column of the specified row in the table or array. Simply put, this formula is used to find things in a column (horizontally).


Formula:

=HLOOKUP(what to search for, where to look for, column number in the range containing the values ​​to return, returns a near or exact result indicated as 1/TRUE, or 0/FALSE).


14. COUNTIF

Use COUNTIF, a statistical function, to count the number of cells that meet the criteria; for example, to count the number of times a certain city appears in the customer list.


In simple terms, COUNTIF takes the following form:


=COUNTIF(Where do you want to search?, What do you want to search?)


For example:


=COUNTIF(A2:A5,"London")


=COUNTIF(A2:A5,A4)


15. MATCH

The MATCH formula searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values ​​5, 25, and 38, the formula =MATCH(25,A1:A3,0) will return the number 2, because 25 is the second item in the range.

Previous Post Next Post

Contact Form