14 Complete Excel Formulas (Main/Basic), Functions, & Uses

Loop.co.id – Did you know? complete Excel formulas is that important? Of course, it is important to make the job easier, the results are more accurate than manual calculations. By knowing complete Excel formulas, work is lighter, faster and more effective.

Many job vacancies require the applicant to master MS. Excel. So, understanding complete Excel formulas will make it easier for those of you who are looking for work. Come on, you can read several types of Excel formulas which we will discuss below.

Various Complete Excel Formulas and Their Functions

Below we will discuss complete Excel formulas in as simple a language as possible so that they are easier to understand. It’s a good idea to read this article while opening Ms. Excel and practice directly one by one, good luck!

1. SUM

The formula used for addition adds numbers from one cell to another.

Example :

If you need value data from the sum of cells A2 to A4, then you can simply use the formula =SUM(A2:A4)

=SUM(A2:A4)

2. COUNT

This formula is used to calculate how many cells contain numbers. So, cells that are empty or contain letters etc. other than numbers, will not be counted.

Example :

If you want to count how many columns contain numbers from cells A1 to H1, you can use the count formula.

=COUNT(A1:H1)

3. COUNT

The function of this formula is almost similar to the Count formula. The difference is that if the count only counts the number of columns that contain numbers, this count counts all the columns that contain them, including letters.

Example :

If you want to know how many columns are not empty from Cells A1 to H1, then you can use this formula.

=COUNTA(A1:H1)

4. AVERAGE

This formula has the same function as average. Average will calculate the average number of cells we want.

Example :

If you want to calculate the average value from cells A2 to A6 then use the average formula.

=AVERAGE(A2:A6)

5. IF

This formula does look complicated, but it is very important. This formula can recognize data based on certain logic, usually in the form of true or false.

Example :

If you want to know whether cell A1 is bigger or smaller than cell B1, then use this If formula. If it is greater you want the word correct to appear and if it is false you want the word wrong to appear, then the formula is:

=IF(A2>B2;”True”;”False”)

6. MAX & MIN

Just as the name suggests, this formula has a function to find the highest and lowest values ​​in data.

Example 1:

For example, if you want to find the highest value from cells A2 to A6, you can use the Max formula.

=MAX(A2:A6)

Example 2:

If you want to find the smallest value from cells A2 to A6 then you have to use the Min formula.

=MIN(A2:A6)

7. TRIM

Surely you have experienced difficulty processing data because of excess spaces which cause errors? That’s what the Trim formula is for, this formula functions to remove excess spaces in cells.

Notes :

You must remember that this formula can only be used in one cell so it cannot be used or applied in several cells at once. Or if you want to use it in several cells, the formula must be written one by one.

=TRIM(A1)

8. AND

This formula is almost similar to IF, both are logical functions. With this formula you can find out whether the contents in a cell are true or false.

Example :

If you want to know whether the number in cell A1 is more than 50 and less than 100, then the formula is:

=AND(A1>50;A1<100)

If the result shows TRUE then the number meets the criteria, if it says FALSE then the number does not meet the criteria.

9. OR

Almost similar to the AND formula, this formula also has the function of finding out whether the numbers in a cell are right or wrong. The difference is that the AND formula requires a number to meet all criteria to be said to be TRUE, while OR only needs to meet one criterion.

Example :

If you want to know whether the A1 value meets one of the criteria of less than 50 or perhaps greater than 100, then you can use the following formula:

=OR(A1<50;A1>100)

Later, if the value meets one of the criteria, TRUE will appear.

10. NOT

This formula is actually the opposite of the two formulas above Or and And. TRUE appears when the number does not match the criteria.

Example :

If you want to know whether the A1 value is not more or less than 50, then you can use the NOT formula.

=NOT(A1>50)

If the A1 result is less than 50 then the result is TRUE, otherwise if it is more then the result is FALSE.

11. LEN

This formula has the function of calculating the number of characters in one cell, including periods, spaces and commas.

Notes :

Just like the TRIM formula, this formula can only be applied to one cell and cannot be applied to several cells at once.

=LEN(A1)

12. AREAS

If you want to calculate the number of areas then this formula is suitable. This Areas formula has the function of calculating the selected finished area.

Example :

If you want to calculate the total area from A2 to C6 then use this formula:

=AREAS(A2:C6)

13. VLOOKUP

This formula does look complicated, but if you are good at using it it will really speed up the work. This formula has a function to calculate all data arranged vertically.

This formula is a little different from the others, the composition or structure of the formula has 4 parts:

=VLOOKUP(Lookup-value;Table-array;Col-index-num;Range-lookup)

Explanation :

  • Lookup value: the value that is the key to searching for data
  • Table array: a table from which data is taken and will be searched based on lookup values
  • Col index num: the number in the array table from which we will retrieve data
  • Range lookup: filled with TRUE or FALSE, the aim is so that we can determine whether we want the exact same results or just close ones. This option can be filled in or blank.

True: used to get a lookup value in the table array with the exact same or close results

False: will only find the exact same results, if you don’t find the exact same then an error will appear

14. HLOOKUP

This formula is almost similar to Vlookup, just as complicated and has a 4 part formula structure too. The difference is that if Vlookup calculates vertical data, Hlookup calculates horizontal data.

Hlookup formula sequence:

=HLOOKUP(Lookup-value;Table-array;Row-index-num;Range-lookup)

The explanation:

The other formula has been explained, the same as vlookup, the difference is only in row index num

Row index num: the order of rows to be viewed

How about it, isn’t it quite complicated to learn the Vlookup, Hlookup formula from other Excel formulas? There are still several more Excel formulas that we have not discussed. Keep learning complete Excel formulas in some of our other articles to strengthen your MS skills. Your Excel.

Originally posted 2023-02-01 08:51:55.


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *