Excel Formula With Example

1.           Plus                        =SUM(G4+ G5)                                                     Addition.

2.           Subtraction        =SUM(G4 – G5)                                                     Subtraction.

3.           Multiplication   =SUM(G4 * G5)                                                     Multiplication.

4            Division               =SUM(G1 / G5)                                                     Division.

5            Product                 =PRODUCT(G5, G6)                                             Multiplication.

6.           Max                 =MAX(A5 : H5)                                                           Greater Value.

7.           Min                 =MIN(A5 : A11)                                                           Less Value.

8.           Lower             =LOWER(G5)                                                                Lower Case.

9.           Upper               =UPPER(G5)                                                                  Upper Case.

10.         Proper              =PROPER(G5)                                                               Title Case.

11.         Count               =COUNT(A5 : A11)                                                     Count Value.

12.         Counta             =COUNTA(A5:A11)                                                     Count All Value.

13.         CountBlank     =COUNTBLANK(A5 : A11)                                         Blank Value.

14.         If                      =IF(G5 > 0 , ”PROFFIT” , ”LOSS”)                           Profit or Loss.

15          Total               =SUM(G1 : G7)                                                             Total.

16.         Percentage       =SUM(G5 * 100 / 500)                                               Percentage 

17.       Grade                 =IF(G5 >= 80 , ”A” , IF(G5 >= 70 , ”B+” ,

                           IF(G5>=60 , ”B” , IF(G5 >= 50 , ”C” ,

                           IF(G5 >= 40 , ”D” , IF(G5 <= 39 , FAILL”))))))      Grade of Mark sheet.

18.         Average           =SUM(G5 / 5)                                                    Average of Mark sheet. 

19.       Remarks            =IF(G5 >= 80 , ”EXCELLENT” , IF(G5 >= 70 , ”VERY GOOD” ,

                                  IF(G5 >= 60 , ”GOOD” ,IF(G5 >= 50 , ”NICE” , IF(G5 >= 40 , ”NORMAL”,

                                  IF(G5 <=39,”DULL”))))))                              Remarks of Mark sheet.

20.        SumIf                     =SUMIF(G1 : G5 , ”> 500” , G1 : G5)                 Greater Value.

21.         Roman                    =ROMAN(G5)                                                   Roman Value.

22.        Len                         =LEN(H5)                                                      Length of Characters.

23.        Date                       =DATE(Year, Month, Date)                               Date Format.

24.        Now                       =NOW()                                                       Current Date & Time.

25.        Number                  =N(G5)                                                                Number

26.        Text                       =T(G5)                                                                  Text.

27.        Trim                       =TRIM(G5)                                                         Number or Text.

28.        Concatenate        =CONCATENATE(G1, H1)                                Concatenate Values

29.        IsText                   =ISTEXT(G5)                                                        True / False.

30.        IsNonText           =ISNONTEXT(G5)                                                 True / False.

31.         Average                 =SUM(G3+G4+G5)/3                                              Average.

32.        Average                 =AVERAGE(I5:N5)                                                 Average.

33.        Average                 =AVERAGEA(I5:N5)                                               Average.

34.        Character             =CHAR(G5)                                                            Character.

35.        Code                       =CODE(G5)                                                            Code.

36.        Left                        =LEFT(G1, 7)                                                  Left Side Character.

37.        Right                      =RIGHT(G1, 5)                                              Right Side Character.

38.        Mid                         =MID(G5, 7, 11)                                             Mid Side Character.

39.        RepT                        =REPT(G5, 7)                                                 Repetition Time Data.

40.        CountIf                 =COUNTIF(A1 : A31, ”A”)                                      Absent.

41.         CountIf                 =COUNTIF(A1 : A31, ”P”)                                      Present.

42.        CountIf                 =COUNTIF(A1 : A31, ”L”)                                      Leave.

43.        Exact                     =EXACT(G5, G6)                                                  Match Value.

44.        Duplicate Data    =COUNTIF(A1 : A11, A1)                                       Duplicate Value.

45.        VlookUp                 =VLOOKUP(H5, A1 : E7, 3, FALSE)                  Search Column Data.

46.        HlookUp                =HLOOKUP(H5, A1 : E7, 3, FALSE)                  Search Row Data.

47.        Transpose             =TRANSPOSE(Y5)                                        Vertical to Horizontal.

48.        Today                    =TODAY()                                                      Current Date Format.

49.        Year                       =DATEDIF(DOB, CURRENT DATE, “Y”)             Total Years

50.        Month                   =DATEDIF(DOB, CURRENT DATE, “M”)              Total Months

51.         Day                         =DATEDIF(DOB, CURRENT DATE, “D”)              Total Days

52.        hyperlink              =HYPERLINK("#'Sheet3'!D20","click here")         Hyperlink