Excel Formulas With Examples

20 Advanced Excel Formulas With Examples You Must Know

Posted on

Do you know? It turns out that using Microsoft Excel is not as complicated as it looks, you know. Of the many data that usually exist in Excel, you can actually process it easily. Huh, easy? Yep, provided you have to know and understand excel formulas.

Mastering this software, of course, will really help your various jobs, such as administrative, finance, accounting, marketing, operational, and other needs. Therefore, it is not uncommon for some job vacancies to list Microsoft Excel skills as one of the requirements. For those of you who want to process data easily, here is a complete Excel Formulas With Examples that you can use in your work.

Top 20 Excel Formulas You Should Know (With Examples)

SUM

If you want to ask, “what is the formula to find the total or how to calculate the total in excel?” then the SUM formula is the answer.

Simply put, you can use the SUM formula to add data or numbers in cells on the worksheet. In other forms, you can also use it to perform summation in more forms such as data in the range (rows and columns) that you choose. For example, looking for the amount or total of sales revenue data.

Meanwhile, the formula is =SUM(number1:number2). (number1:number2) is the data or rows and columns that you select. In addition, also pay attention to the use of separators between data in your excel, whether using a colon (:), comma (,), or semicolon (;). In its use, you just enter the formula and data in the desired row and column and then press enter.

all excel formulas with examples in excel sheet download

In the example above, you can see that there is an income report cell for 7 months from January to June which is divided into 4 weeks. In the example, you want to find the total income for the month of January. Then the formula used is =SUM(B3;C3;D3;E4). Where (B3;C3;D3;E4) is the column code from week 1 to 4 of January. After you enter the formula, then press enters to display the results.

Also Read: 70 Inspirational Quotes for Work to Inspire You to Be Success

AVERAGE

The AVERAGE formula is used to find the average value of the numbers in your data. The formula used is =AVERAGE(number1:number2) based on the row and column you select. As with SUM, pay attention to the data separators in the formulas used, such as colons (:), commas (,), or semicolons (;).

Unlike the SUM formula, this time we will find the average income in January by using the formula =AVERAGE(B3;C3;D3;E3). After entering the range you choose, then press enter to get the results.

COUNT

Have you ever felt confused when you wanted to count the amount of data that was already filled in the table in your worksheet? Relax, by using the COUNT formula, you can calculate it even with a large amount of data. The Count formula is used to count data that is filled in cells and only numbers. The formula used is =COUNT(value1;[value];..) or =COUNT(range).

excel formula list

In the example above, we will count the number of entries that have been recorded or exist in the cell. The formula used is =COUNT(B3:E8) to calculate the total income for 4 weeks. Which (B3:E8) is the selected range. In the cell above there are some data that are filled and empty. After entering the formula, you will get the value of the data that is filled in, which is 21.

COUNTBLANK

If COUNT functions to count data that has been filled in, then COUNTBLANK is a formula to count data in the form of empty numbers in cells. The formula used is =COUNTBLANK(range).

useful excel formulas

In the example above, we can use the COUNTBLANK formula to find how many data are empty in the number of entries. The formula used is =COUNTBLANK(B3:E8) and shows the results of 3 blank data.

COUNTA

COUNTA is a formula used to find data that is filled in cells in the form of numbers or text. The formula used is =COUNTA(value1; [value2];..) or =COUNTA(range).

excel formulas cheat sheet

In the example above, we will look for the amount of data that is filled in the “status” column. To calculate how much data has been filled with Unpaid/Paid status, the formula used is =COUNTA(G3:G8). After entering the formula, you will get the result that is 4 data filled.

SINGLE IF

Simply put, the IF formula is used to find the result of a calculation with two possible results such as “true/false”. However, the criteria or results in this formula are not always “true/false”. You can also replace it with other criteria, such as “Good-not good”, “Passed-did not pass”, and so on.

In the SINGLE IF formula, we will look for data calculations using only one criterion. The formula is =IF(logical_test; [value_if_true]; [value_if_false]).

Logical_test is the data that you choose with a calculation that will be measured by criteria. While [value_if_true]; [value_if_false] is the criteria that you will enter in the calculation of the data.

advanced excel formulas pdf

In the example above, we will calculate the status of the stock item using the “SAFE or REQUEST” criteria. The first step in using the IF formula is to determine the logical_test, by taking one of the data you want to calculate, namely, G3 with a value of 210. While 200 is the sample measurement. The formula used is =IF(G3>=200; “SAFE”; “REQUEST”).

If the formula is discussed it will be, IF(G3 is greater than (>) or equal to (=) 200 then give SAFE, otherwise give REQUEST). The description shows if the stock is more than 200 then it will be in SAFE status, if not then REQUEST status. Since the selected data is worth 210, the status will be SAFE.

MULTI IF

In contrast to SINGLE IF which only uses one criterion, in MULTI IF, you will enter more than one criterion in the calculation. The formula used is =IF(logical_test; [value_if_true]; [value_if_false] by adding a few more IFs after the first formula.

excel functions

In the example above, we will look for the calculation of grade scores on students. The criteria used are in the form of letters to become grade values ​​such as A, B, C, D, and E. In determining the logical_test above, we take one of the data we want to calculate, namely, B3 with a value of 65. Meanwhile, the sample size is listed in the description table.

The formula used is =IF(B3>=90;”A”;IF(B3>=70;”B”;IF(B3>=60;”C”;IF(B3>=50;”D”) ))). In the formula you can see, there are several IFs in one formula.

If the formula is discussed it will become, IF (B3 is greater than or equal to 90 then give A, IF B3 is greater than or equal to 70 then give B, IF B3 is greater than or equal to 60 then give C, IF B3 is greater or equal to 50 then give D). Then according to the calculation, the student’s score will be converted to a predetermined grade. Like, Gloria Putri who got grade A with a final score of 95.

COUNTIF

Not much different from COUNT, COUNTIF shows the calculation in a more specific way and uses additional criteria such as the IF formula. The formula used is =COUNTIF(range; criteria).

what are the 5 functions in excel

In the example data above, we will calculate the number of “Unpaid” in the status column. The formula used is =COUNTIF(G3:G14; “Unpaid”). (G3:G14) is the range or row and column that you select. While “Unpaid” is a criterion in the use of the formula. After you enter the COUNTIF formula, it will show the number of “Unpaid” as many as 8.

VLOOKUP

The VLOOKUP formula functions to find a value that is in the table you are working on, sourced from another table. This formula can also be used to retrieve data from other tables accurately and practically, so it really helps you if you have to analyze a lot of data. The formula used is =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup

(lookup_value) is the column that will be adjusted to the values ​​in the reference table. (table_array) is the source table from which the data will be retrieved. (index_num) is the column number in the table from which you want to retrieve data. Meanwhile (range_lookup) functions as a marker, if the value you want to look for does not exist in the reference table, then the value will not be displayed or it will be wrong. In addition, the order of taking column numbers must be calculated from the far left.

excel formula list

For example, the table above contains data on salaries and bonuses based on employee skills. The table (Your Cafe) is a reference table, while the table below is the table we are working on or analyzing.

SUMIF

Next Excel Formulas With Examples is the SUMIF excel formula which functions to add up data with the criteria you have specified. Basically, we give a command to add (SUM) a value, but with certain criteria (IF). The SUMIF formula is =SUMIF(range;criteria;[sum_range]).

The range is the cell data of the criteria you want to find the value for. Then criteria are the name of the criteria. While sum_range data from the value you want to sum.

excel formulas pdf

Also Read: Best Freelance Websites to Work

SUMIFS

If SUMIF is used to add values ​​with one criterion, then the SUMIFS formula functions to add data with two or more criteria. The formula used is, =SUMIFS(sum_range;criteria_range1;criteria1;criteria_range2;criteria2:…). It can be seen, that if in SUMIF, sum_range is in the back position, then in SUMIFS, sum_range is the first value we enter into the formula.

Sum_range is the value of the data you want to sum. criteria_range1 is the data column of the first criteria, then criteria1 is the first criterion of the values ​​you want to add up. Next is criteria_range2 which is the column of the second criterion, then criteria2 is the criterion of the value you want to add up.

ROUND

The Round formula is used to change the value in the form of a decimal number (using a comma) to a number that has a round value. The formula is, =ROUND(number; num_digits). (number) is the column where the number or value you want to convert to an integer is located. Whereas (num_digits) is the number of rounding digits you want to apply to the number after the comma. The number of digits can be the number 0, 1, 2, and so on depending on the number of digits behind the comma.

useful excel formulas

In the example above, we will round the number in James’ value to an integer. The formula used is =ROUND(B3;0). B3 is the column number that is in the value you want to change. While 0 is a number that converts a decimal number to an integer. After we enter the formula, then 67.25 will be rounded to 67.

However, if you want to round to 1 or 2 digits after the comma, then you can enter 1 or 2 in (num_digits). For example, the initial value is 67.2593, then with the formula =ROUND(B3;2), only the last two numbers are rounded after the comma, which is 67.26.

LOWER

The Lower formula is used to change the font format from UPGRADE to lowercase. For example, in the example, you want to change the words “BAMBANG SUKARNO” to “Bambang Sukarno”. Then the formula used is =LOWER(text). LOWER is the formula, while (text) is the letter text that you will change. the writing of the text is added by double quotes or double quotes ( “ ”).

excel functions

UPPER

While the Upper Excel Formulas With Examples is used to change the font format from Small to BIG. For example, in the example, you want to change the words “Bambang Sukarno” to “BAMBANG SUKARNO”. Then the formula used is =UPPER(text). UPPER is the formula, while (text) is the text of the letters that you want to change.

advanced excel formulas pdf

TRIM

The TRIM formula is usually used to correct extra-spaced sentences in Excel. Not infrequently we unknowingly overpress the space bar when filling in data. This space error cannot be underestimated. Because besides being able to cause errors, this can also have an impact on the application of other formulas. Therefore, here is the formula =TRIM(text). Using this formula is quite easy. You just enter the formula and select the text or the location of the sentence you want to correct the spacing.

In the example above, you can see that we will correct the writing of the book title that has been recorded. The title of the previous book was “_Green Wine__Tanpa Rasa” we will change to the formula = TRIM(B3). =TRIM is the formula and (B3) is the location of the text that we will fix. In the title, there are excess spaces before the word “Wine” and after the word “Green”. So that after the formula is entered it will produce “Green Wine Without Flavor”.

LEN

The next Excel Formulas With Examples is LEN which functions to count the number of characters in a cell including spaces. This formula is usually very helpful for those of you who work in the printing or administration field. The formula used is =LEN(text). Using this formula is quite easy. You just enter the formula and select a cell from the text for which you want to know the number of characters.

If you look at the example above, you will find out how the LEN formula works. In this case, we will count the number of words in the book titles that have been recorded. The formula used is, =LEN(F3). F3 is the cell name of the text that we will count the number of characters. After we enter the formula, the result is 23 characters.

CEILING

The CEILING Excel Formulas With Examples is used to round values ​​or numbers by multiples of ten and above. You can use this formula for accounting purposes and so on. The formula used is =CEILING(number; significance). The number is the number in the cell that you want to round. While the significance is the amount of rounding you want, such as, 10, 100, 1000, and so on.

FLOOR

Contrary to the CEILING formula which is used to round numbers up. The FLOOR formula is used to round numbers to multiples of 10 and below. The formula used is =FLOOR(number; significance). Similar to CEILING, the number is the number in the cell you want to round. While the significance is the amount of rounding you want, such as 10, 100, 1000, and so on.

MAX

The Max formula is used to find the highest value in data. This formula is very helpful when you have a lot of data. The Max formula is =MAX(number1;[number2];..). Number1 in the formula is the location of the data or cell for which you want to find the highest value. In addition, if you want to find the highest value of more than one data, you just need to select the location of the data (range).

MIN

The next Excel Formulas With Examples is the inverse of MAX. Yep, that is the MIN formula. The MIN formula is useful for finding the lowest value from existing data. The formula used is not much different, namely, =MIN(number1;[number2];..). Number1 in the formula is the location of the data or cell for which you want to find the lowest value.

Also Read: Reasons and Benefits Why: I Want to Work From Home

Those are Excel Formulas With Examples that can make data processing faster and more practical.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.