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!
The formula used for addition adds numbers from one cell to another.
If you need value data from the sum of cells A2 to A4, then you can simply use the formula =SUM(A2:A4)
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.
If you want to count how many columns contain numbers from cells A1 to H1, you can use the count formula.
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.
If you want to know how many columns are not empty from Cells A1 to H1, then you can use this formula.
This formula has the same function as average. Average will calculate the average number of cells we want.
If you want to calculate the average value from cells A2 to A6 then use the average formula.
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.
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:
6. MAX & MIN
Just as the name suggests, this formula has a function to find the highest and lowest values in data.
For example, if you want to find the highest value from cells A2 to A6, you can use the Max formula.
If you want to find the smallest value from cells A2 to A6 then you have to use the Min formula.
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.
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.
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.
If you want to know whether the number in cell A1 is more than 50 and less than 100, then the formula is:
If the result shows TRUE then the number meets the criteria, if it says FALSE then the number does not meet the criteria.
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.
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:
Later, if the value meets one of the criteria, TRUE will appear.
This formula is actually the opposite of the two formulas above Or and And. TRUE appears when the number does not match the criteria.
If you want to know whether the A1 value is not more or less than 50, then you can use the NOT formula.
If the A1 result is less than 50 then the result is TRUE, otherwise if it is more then the result is FALSE.
This formula has the function of calculating the number of characters in one cell, including periods, spaces and commas.
Just like the TRIM formula, this formula can only be applied to one cell and cannot be applied to several cells at once.
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.
If you want to calculate the total area from A2 to C6 then use this formula:
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:
- 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
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:
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.