Excel Function to be a expert of MS-EXCEL.

Dear Friends learn with me EXCEL FUNCTION

 1. ABS:- Returns the absulate value of the number without its sign

Syntax:-  =abs(number)

Example:-                 =abs(-100.95)

                                   =100.95 Ans

If  A85=100     then =abs(a85)

                                  =100 Ans

2.   =now() ,  =today()   These two function return the current date and time according to your system date and time

 
Insert this function in a cell and then right click on the cell
Then go to               1. Format Cell
                                2. Custom
                                3. Select a date & time format in the list given
Note:- When you are working with the worksheet it will be automatically updated  and given the system time.
 
 
3. Avarage:-Its give the average (arithmetic mean) of the arguments.
Syntax:- =average(number1,number2,number3 etc) and
             =average(number1 to number2)
Example:- =average(100,200,300,400,500)
                =300 Ans.
If in a excel sheet A1=100, A2=200, A3=300, A4=400, A5=500
                =average(A1:A5)
                =300 Ans.
Tip  When averaging cells, keep in mind the difference between empty cells and those containing
the value zero, especially if you have cleared the Zero values check box on the View tab
(Options command, Tools menu).
Empty cells are not counted, but zero values are.
A
B
C
1
100
100
2
100
100
3
100
100
4
100
100
5
0
 
6
=average(B1:B5)
=80
=average(C1:C5)
=100

3. Power Function: - This is the mathematical function. The Power function returns the result of a number raised to a given power.

Number: - is the base number. It can be any real number.           
Power: -   is the exponent to which the base number is raised.
Syntax of the Power Function:-
                              =POWER(NUMBER,POWER)
 
Example 1:- If number=10 and power=2
                    Then, =power(number,power)
                               =power(10,2)
                               =100 Answer.
 
Example 2:- If number=95.5 and power=2.5
                    Then, =power(number,power)
                              =power(95.5,2.5) 
                              =89126.82 Answer.
 
Example 3:- If number=10 and power=4/5
                    Then, =power(10,4/5)
                               =6.31 Answer.

 

SQRT Function:-
                             Sqrt function is math function which is returned a positive square root.
If the number you have entered is negative , SQRT function returns the error value .
 
Syntax:- =sqrt(number)
Enter the number for which you want the square root.
 
Example:-
  1. =sqrt(16)
=4 Answer.
 
  1. =sqrt(-16)
=#NUM Answer
 
     3. =sqrt(abs(-4))
            =2 Answer

 

IF Function:-
                        This function returns an answer according to conditions. Returns one value if a condition you specify evaluates to True and another value if it evaluates to False.This function is one of Excel’s most useful and most used functions.
                        Testing whether conditions are true or false and making logical comparisons between expressions are comman to many tasks. You can use the AND,OR,NOT, and IF function to create conditional formulas.
 
Syntax of IF Function:- =IF(Logical_Test, Value_if_true, Value_if_false)
 
  1. Logical Test: - Enter the condition that you want to check.
  2. Value If True: - Enter the value to return if the condition is True.
  3. Value If False: - Enter the value to return if the condition is False.
 
Example of IF Function:-
                                                                       
1. =IF(100<101,”Right”,”Wrong”)                Answer – Right
 
2. =IF(100<=101,”Right”,”Wrong”)              Answer – Right
 
3. If cell A1=100,B1=50,B2=100,B3=150 and B4=200 then
    =IF(A1=100,SUM(B1:B4),””)
 
4.

Item
Price of Shop-1
Price of Shop-2
Mobile
1500
900
Keyboard
500
900
Bag
500
925

 
=IF(B2>C2,"Shop two is better for you","Shop one is better for you")
Answer – Shop two is better for you.
 
=IF(B3>C3,"Shop one is better for you","Shop one better for you")
Answer – Shop one is better for you.
 
 
COUNTBLACK Function:-
                               This function counts the number of empty cells in a specified range of cells. Cells with zero value are not counted. Cells with formula that returns “” (empty text) are also counted.
 
Syntax of the COUNTBLACK Function: - =COUNTBLACK (RANGE)
Range:- Range is the range from which you count the black cells.
 
 
A
B
1
10
2
2
20
10
3
30
29
4
=IF(A4>A3,"",A5)
56
5
50
 
6
 
51
7
70
 
8
80
21
 
Example:-
=countblank(B1:B8) = 2  answer
=countblank(A1:A8) = 2 answer
=countblank(A1:B8) = 4 answer
 
 
COUNTIF Function: -
Counts the number of cells within a range that meet the given criteria. In other words, You can count cells that meet a specific criterion.
 
Syntax of the COUNTIF function: - =COUNTIF(RANGE,CRITERIA)
Range: - Range is the range from which you want to count the cell.
Criteria: - Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted.
 
 
A
B
1
10
=countif(A2>A1,”RAM”,””)
2
20
10
3
30
29
4
RAM
56
5
50
RAM
6
RAM
51
7
70
RAM
8
80
21
 
Example: - =COUNTIF(A1:B8,"RAM")
                   =5 Answer.
 
                   =COUNTIF(A1:A8,”RAM”
                   =2 Answer.         
 
                   =COUNTIF(B1:B8,”RAM”)
                   =3 Answer

 

Go to Enjoy This Tricks Page

Go to Short Cut Key of Ms-Office

Back to the top