Important function in Excel
some other function in excel;
Important function in Excel
Sqrt Function:
The sqrt function returns the square root of a number. The syntax for sqrt function is sqrt (number
The number is s positive number whose square is required. If negative number is entered in the number parameter, the sqrt function will the return the #NUM! Error.
Suppose the worksheet contains the following values:
Based on the Excel spreadsheet above:
| =Sqrt(A1) | Would return 5 |
| =Sqrt(A2) | Would return 5.796550698 |
| =Sqrt(A3) | Would return #NUM! |
| =Sqrt(82.6) | Would return 9.088454214 |
Fact function
The fact function returns the factorial of a number. The syntax for the fact function is fact (number)
Number is a numeric value.
Based on the Excel spreadsheet above:
| =Fact(A1) | Would return 6 |
| = Fact A2) | Would return 6 |
| =Fact (A3) | Would return 1 |
| = Fact (A4) | Would return #NUM! |
| = Fact(4) | Would return 24 |
Count blank function:
The count blank function count the number of empty cells in a range. The syntax of count blank function is:
Count bank (Range)
Range is the range of cells to count empty cells.
Based on the excel spreadsheet above’;
| =count Blank (A1:A4) | Would return 1 |
| =count Blank (A1:A6) | Would return 3 |
| =count Blank (A1:B4) | Would return 3 |
Lower function
The lower function coverst all letters of given string to lowercase. The dcharacter that are not letters are not affecte by this finction the syntax for the Lower function is:
Lower (text)
Text is the string to covert to lowercase.
Based on the Excel spreadsheet above:
| =lower(A1) | Will return “alphabet soup” |
| =Lower(A2) | Will return “techonthenet” |
| =Lower(“Excel”) | Will return “excel” |
Upper function
The upper function converts text to all uppercase. The syntax of upper function is Upper(text)
Text is the string that you wish to convert to uppercase.
Based on the excel spreadsheet above.
| Upper(A1) | Would return “ALPHABET SOUP” |
| = Upper(A2) | Would return “TECHONTHENET” |
| = Upper(“Excel”) | Would return “EXCEL” |
| = Upper(“123abc”) | Would return “123ABC” |
Power function
The power function teturn the retuns the result a number raised to go a given power. The syntax for the Power function is:
Powr (number, power)
The number is a base number. The power is exponent used to raise the base number to suppose a worksheet contains the following numbers.
| =Power(A1,A2) | Would return 81 |
| =Power(A1,A3) | Would return 140.2961154` |
| =Power(A2,2) | Would return 16 |
Product function
The product Function:
The Product function multiplies the numbers and returns the product. The syntax for the Producnt function is:
Product (numbe1r, number2…..number-n)
numbe1r, number2…..number-n are the numbers to multiply together. There can be up to 30 numbers enterd.
Suppose a worksheet contains the following values.
Base on the Excel spreadsheet above.
| =Product(A1,A2) | Would return 12 |
| = Product(A1,A2,A3) | Would return 54 |
| = Product(A1, A2,A3,A4) | Would return -366.12 |
| = Product(A1,A2,A3,A4 -2) | Would return 732.24 |
Mod function
The Mod function returns the remainder after a number is divided by a divisor. The syntax of Mod function is:
Mod(number, divisor)
Number is a numeric value whose remainder you wish to find.
Divisor is the number used to divide number parameter. If divisor is 0,Mod function will retun the #DIV/0! Error.
Based on the excel spreadsheet above:
| =Mod(A1,A2) | Would return 2 |
| = Mod(A1,A3) | Would return -7 |
| = Mod(34,A2) | Would return 0 |
| = Mod(34,0) | Would return #DIV/0! |
Today Function
The today function returns the current system date. It refreshes the date whenever the worksheet recalculates. The syntax of Today function is:
Today()
The spreadsheet continues to display this value. Until the worksheet recalculates. The user can reclclate the value by pressing F9 key.
Now function
The Now functions returns the current system date and time. Its syntax is as follows:
Now()
The user can recalculate the function by pressing F9 key.
Count if function
The countIf function counts the number of cells in a range, that meets a given criteria.
The syntax of countif function is:
CountIf((range,criteria)
Range is the range of cells that you want to count based on the citeria.
Criteria are used to determine which cells to count.
Based on the Excel spreadsheet above:
| =CountIf(A2:A7,D2) | Would return 1 |
| = CountIf(A:A,D2) | Would return 1 |
| = CountIf(A2:A7,”>=2001”) | Would return 4 |
Sumif function
The sumif function adds all numbers in a range of cells, based on a given criteria. The syntax of sumif function is:
sumIf( range,criteria, sum,-range)
Range is the range of cells that you want to apply the criteria against.
Criteria are used to determine which cells to add.
Sum-range are the cells to sum.
| =SumIf(A2:A6,D2,C2:C6) | Would return 218.6 |
| =SumIf(A:A,D2,C:C) | Would return 218.6 |
| =SumIf(A2:A6,2003,C2:C6) | Would return 7.2 |
| =SumIf(A2:A6,”>=2001”,C2:C6) | Would return 12.6 |