Excel Formulas That You Need To Familiarize Yourself With:

SUM

Just as the name suggests, you can expect the SUM function to help you add 2 or more numbers. For further assistance, you can also use the cell references together with the formula. For as long as there are numbers in the reference cells, you can have as many numbers as possible added together.

Example of SUM formula in excel


=SUM(A1:B5)

=SUM(5, 5)

=SUM(A1, B1)


COUNTA

This function allows you to count the numbers of non-empty cells in a given range. What the function does is that it allows you to count cells that already have numbers together with any other characters found in the cells. The function works with all data types. 

Example of COUNTA formula in excel


=COUNTA(A1:A10)

COUNT

You can use the formula function to count the numbers of the different cells in a range that have numbers in them. The formula cannot work without numbers. 

Example of COUNT formula in excel


=COUNT(A1:A10)

TRIM

This is a very useful function that was designed to help users get rid of any sorts of spaces found in cells. The only exception is with single spaces between words. Do not allow yourself to pull out data from your database with extra spaces because it can wreak havoc when it comes to comparing using IF statements or VLOOKUP's.

Example Excel TRIM formula


=TRIM(A1)

LEN

Whenever this function is included in a function, you can expect it to count the number of characters in that given cell. You need to note that this function also counts the spaces between words. If you do not need to count the spaces, do not use this function. 

Example Excel LEN formula


=LEN(A1)

VLOOKUP

Anyone who has used excel for a while can attest to the fact that the VLOOKUP formula is the most used formula. What the formula really does is that it looks for a value that is found in the leftmost column of a table and goes ahead and returns a value in the same row from a column that you'd have specified. 
Below example searches for “TextToSearch” in array of cells from A1 to B10, the last parameter FALSE is used to do exact match search.

Example Excel VLOOKUP formula


=VLOOKUP("TextToSearch",A1:B10,2,FALSE)

AVERAGE

This function will help you to calculate the average value of the series of numbers found in between the specified cells. In this case, the function will calculate the averages of the numbers found in the cells B1 to B3 and give you an optimal result.

Example Excel AVERAGE formula


=AVERAGE(B1:B3)

MIN

This function can be used to help one calculate the lowest number in a given series of numbers. In the example above, the formula will give you the lowest number found in the series between cells B1 to B3. 

Example MIN in excel formula


=MIN(B1:B3)

MAX

The MAX function helps you to find the highest number in a given series of numbers. In the given example, the function will get you the highest figure in the series between cells B1 to B3.

Example MAX in excel formula


=MAX(B1:B3)

IF Function

You can use this function to help you determine whether a given statement is true or false which can then allow you to perform a specified action based on your results. 

Example IF in excel formula


=IF(Criteria,True value,False value)

SUMIF Function

More or less similar to the IF Function. The only difference is that with the SUMIF Function, after finding the values of the set criteria, it will go ahead and Sum up the values of the related cells. 

Example SUMIF in excel formula


Below example will do the sum of all number in A1 to A25 if they are greater than 10
=SUMIF(A1:A25,">10")

COUNTIF Function

The function works the same way as the SUMIF Function with the exception that it counts the fields that match with one another in relation to the set criteria instead of summing them together. 
Below example will do the count of all number in A1 to A25 if they are greater than 10

Example COUNTIF in excel formula

=COUNTIF(A1:A255,">10")

AND Function

Dubbed as a logical function, the AND function helps you to check specified multiple criteria and will give a TRUE value if say ALL the criteria are found to be TRUE. If that criterion is not met, it will return as false.
Below example will do logical AND operation of two conditions and return a boolean value. In this case it must return TRUE

Example AND in excel formula


=AND(1+1=2, 3+3=6)

OR Function

This function is similar to the AND Function. However, the OR Function checks multiple criteria but only requires ONE statement to be true for the entire statement to be TRUE. 
Below example will do logical OR operation of two conditions and return a boolean value. In this case it must return TRUE

Example OR in excel formula


=OR(1+1=2, 3+3=6)

RIGHT, LEFT, MID Function

All these functions are great TEXT functions that are used for manipulating data in a given cell. With the function, you can use it to take any numbers/words in a given cell, pull it in the right or left from them and go ahead to put them in a new cell. 

Example RIGHT in excel formula

Example to get Right part of text, below will return TEXT

=RIGHT('TESTSOMETEXT',4)

Example LEFT in excel formula

Example to get Left part of text, below will return TEST

=LEFT('TESTSOMETEXT',4)

Example MID in excel formula

Example to get Middle part of text, below will return SOME

=MID('TESTSOMETEXT',5,4 )

TAGS : | |