EXCEL

Excel Basics :- 

FUNCTION

•  Count :- It count the number of cells that contain number.

         Syntax : -  =count(A1:A10)

• Countif :- It count the cells based on one criteria.

         Syntax:-   =countif(A1:A10, ">9")
                       It will count the cell whose value is greater than 9.

• Countifs :-  It count the cell based on multiple criteria.

        Syntax :-    =countifs(A1:A10, "Green", B1:B10, ">9")

                          **Here (green) must be an option in Table (A1:A10)

• Sum :- To sum a range of cells

        Syntax :-   =sum(A1:A10)

• Sumif :- To sum cells based on one criteria

        Syntax :-   =sumif(B1:B10,">10")

• Sumifs  :- To sum cells based on multiple criteria.

         Syntax:- =sumifs(C1:C10, A1:A10,"circle", B1:B10, "red")

• IF :- The IF function checks whether a condition is met and return one value, if 'true' and another value if 'false'.

         Syntax:-  =IF(B2>=60,"Pass","Fail")

• AND :- The AND function returns TRUE if all condition are true and returns FALSE if any of the condition are false.

          Syntax:- =AND(B2>=60, C2>=90)

• OR :- The OR function returns TRUE , if any of the condition are true, and returns FALSE, if all the conditions are false.

          Syntax:-  =OR(B2>=60, C2>=70)

• NOT :-  The NOT function changes TRUE to FALSE and FALSE to TRUE

          Syntax :-   =NOT(OR(B2>=60, C2>=60))

• Equalto :-  The equal to operator (=) returns true if two values are equal  to each other.
• Cell Reference :- There are basically three type of cell reference in excel. these are:

1. Relative Reference :-  By default excel uses relative cell reference.  Example :-  =B2*C2
2. Absolute Reference :- To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number.                     Example:- ($H$3) in the formula cell let say E3
3. Mixed Reference :- Sometimes we need a combination of relative and absolute reference.                                                                       Example:-  =B2*C1 -B6,                 =$B2*C1 -  B$6

Excel Text Function

Function to remove Extra Characters

• CLEAN :- It removes all non-printable characters from a supplied text.
• TRIM :- It removes duplicate spaces, and spaces at the start and end of a text string.