📘Common Functions in Excel

🔹 Limitations of Formulas

1. User must know the calculation procedure.
2. Becomes complex with the increase of cell addresses. Cell range notation won't support.

🔹 Functions

A Function is a pre-defined formula in Excel.

• Functions also start with the = sign.
• User must know the purpose and syntax of the function.
• Syntax of any function: =FUNCNAME(arg1,arg2,...)
• Arguments are the values on which the function has to work.
• Arguments can be numbers, cell addresses, or cell ranges.
• All arguments appear inside parentheses, separated by commas.
• There are 10+ groups of functions in Excel for different purposes.
• All pre-defined functions are listed in the Formulas Tab based on their purpose.

🔹 Groups of Functions Based on Arguments

1. No arguments – Example: =TODAY()
2. Many arguments – Example: =SUM(3,4,5,...) (Maximum 255)
3. Limited arguments – Example: =IF(ARG1,ARG2,ARG3)

🔹 Commonly Used Excel Functions

• SUM – Returns the addition result of given arguments.
  Syntax: =SUM(arg1,[arg2],...)
• AVERAGE – Returns the arithmetic mean of given numbers.
  Syntax: =AVERAGE(arg1,[arg2],...)
• MIN – Returns the minimum value.
  Syntax: =MIN(arg1,[arg2],...)
• MAX – Returns the maximum value.
  Syntax: =MAX(arg1,[arg2],...)
• COUNT – Returns the number of numeric values in a range.
  Syntax: =COUNT(arg1,[arg2],...)
• MINA, MAXA, COUNTA – Work with text values also, treating them as value '0'.
• AND – Returns TRUE when all arguments are TRUE, otherwise FALSE.
  Syntax: =AND(arg1,[arg2],...)
  Example: =AND(5>7,12<20)

🔹 Nesting of Functions

A function or formula can be nested inside another function.

Example: =AND(MAX(A1:E1)>100)

🔹 IF Function

The IF function takes 3 arguments.

Syntax: =IF(LogicalTest, Val1, Val2)

• arg1 – Logical test that returns TRUE or FALSE.
• arg2 – Result when arg1 is TRUE.
• arg3 – Result when arg1 is FALSE.

Examples:

• =IF(A1>B1,"A1 is big","B1 is big")
• =IF(AND(B4>=35,C4>35,D4>35,E4>35,F4>35),"PASS","FAIL")

🔹 RANK Function

Used to assign ranking for given numbers.

Syntax: =RANK(Number, Range, Order)

Example: =RANK(G6,$G$4:$G$8,0)