A formula is an expression that operates on values in a range of cells. These formulas return a result, even when it is an error.
The SUM() function gives the total of the selected range of cell values. It performs the mathematical operation.
=SUM(A1:C5)
The AVERAGE() function calculate the average of the selected range of cell values.
=AVERAGE(A1:C3)
The MIN() function finds the lowest number in the selected range of cell values.
=MIN(A1:D10)
The MAX() function finds the highest number in the selected range of cell values.
=MAX(A1:D10)
The COUNT() function counts the total number of cells in a range that contains a number.
=COUNT(A1:C4)
The SUBTOTAL() function returns the subtotal in a database. Depending on what you want. You can select either average, count,sum, max, min and others.
=SUBTOTAL(1,A1:C10)
In the subtotal list "1" referes to average.
=SUBTOTAL(4,A1:C10)
In the subtotal list "4" refers to maximum.
The MOD() function works on returning the remainder when a particular number is divided by a divisor.
=MOD(A1,2)
value A1 divides by 2 and return the remainder.
The POWER() function returns the result of a number raised to a certain power.
=POWER(A2,3)
Here the value of A2 multiply 3 times
for example if value of A2 is 10 then 10*10*10=1000
The CEILING() function rounds a number up to its nearest multiple of significance.
=CEILING() function rounds a number up to its nearest multiple of significance.
The nearest highest multiple of 5 for 35.316 is 40.
The FLOOR() function contrary to the ceiling function, the floor function rounds a number down to the nearest multiple of significance.
=FLOOR(A1,5)
The nearets lowest multiple of 5 for 35.316 is 35.
The CONCATENATE() function joins several text strings into one text string.
=CONCATENATE(A1," ",C1)
Suppose A1 having "SOOPRO" and C1 having "INDIA" then result will be "SOOPRO INDIA".
The LEN() function returns the total no. of characters in a string.
=LEN(A1)
Suppose A1 having "SOOPRO" The result will be "6".
The LEFT() function gives the number of characters from the start of a text string.
The MID() function returns the characters from the middle of a text
The RIGHT() function returns the numbers of characters from the end of a text string.
The UPPER() function converts any text string to UPPERCASE.
The LOWER() function converts any text string to lowercase.
The PROPER() function converts any text string to proper case i.e the first letter in each word will be in uppercase, and all the other will be in lowercase.
The REPLACE() function works on replacing the part of a text string with a different text string.
=REPLACE(old_text,start_num,num_chars,new_text)
Here start_num refers to the index position you want to start replacing the characters with. Next num_chars indicate the nof of chars you want to replace.
The SUBSTITUE() function replaces the existing text with a new text in a text string.
=SUBSTITUTE(A1,"I LIKE","HE LIKES")
here A1 is any text. I LIKE is a old text and HE LIKES is new text.
The NOW() function in Excel gives the current system date and time.
=NOW()
The TODAY() function in excel provides the current system date.
=TODAY()
The DAY() function is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.
=DAY(TODAY())
The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December.
=MONTH(TODAY())
The TIME() function converts hours, minutes, seconds, given as numbers to an Excel serial no formatted with a time format.
=TIME(20,40,20)
The HOUR() function generates the hour from a time value as a no from 0 to 23, Here 0 means 12 AM and 23 is 11 PM.
=HOUR(NOW())
The MINUTE() function returns the minute from a time value as a number from 0 to 59.
=MINUTE(NOW())
The SECOND() function returns the second from a time value as a no from 0 to 59.
=SECOND(NOW())
The DATEDIF() function provides the difference between two dates in terms of years, months, or days.
=DATEDIF(A1,A2,"y")
Here A1 is first date and A2 is second date and y indicate Year.
The VLOOKUP() function stands for the vertical lookup that is responsible for looking for a particular value in the left most column of a table.
Arguments for the vlookup function
lookup_value- This is the value that you have to look for in the first column of a table.
table- This indicates the table from which the value is retrieved.
col_index- The column in the table from the value is to be retrieved.
range_lookup-[optional] TRUE= approximate match(default). FALSE= exact match
=VLOOKUP(A1,A2:E7,2,0)
The HLOOKUP() function or horizontal lookup. The function HLOOKUP looks for a value in the top of a table or array of benefits. It gives the value in the same column from a row you specify.
=HLOOKUP(A1,A2:E7,3,0)
The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.
The IFERROR() function returns a value if an expression evaluates to an error, or else, it will return the value of the expression.
The function COUNTIF() is used to count the total number of cells within a range that meet the given condition.
=COUNTIF("Attendance Range","P")
The SUMIF() function adds the cells specified by a given condition or criteria.
PMT() function is used when you want to calculate the monthly payment you need to pay to settle the loan amount.
The RANK() function in Excel returns the order of a numeric value compared to other values in the same list.
=RANK(Number, ref,[order])
The OR() function is used to check more than one logical condition at the same time up to 255, conditions supplied as arguments.