## Youth Skill Development Foundation

### युथ स्किल डेवलपमेंट फाउंडेशन

#### Recognized By: Indian Govt. NITI Aayog  Skill India Digital India Govt. of West Bengal MSME & More # Microsoft Excel basic formulas

As a beginner in excel formula you must need clear concept of below commands. In excel FORMULA and FUNCTION terms are used frequently. They are closely related, but not same. Formula is always start from ‘=’ operator like [ =a1+b1 ]. But Function is a formula with a special name ans purpose like [ =sum(*:*) or =Max etc. ]

Function Arguments- Most function require input to return output. This input in excel is called argument.

Sum Formula-

This formula is used for Addition purpose. it can sum given cell values in any order.

=sum(C3:E3) /or =sum(C3+D3+E3) /or =sum(55+75+87)

Average Formula:

This provide average number of given data.

=Average(C3:E3)

Maximum Formula:

Find maximum value from given range.

=MAX(B1:D3)

Minimum value:

Thi formula find out smallest number from given range.

=MIN(B1:D3)

Product Formula:

This formula multiplies string value of any given range.

=PRODUCT(B1:D3)

Change Case:

Change Case is used to convert data into Uper, Lower or in Proper case.

• =UPPER(A2) – Display cell data in uppercase format.
• =LOWER(A2) – Display cell data in uppercase format.
• =PROPER(A2) – Display cell data in proper format or sentence case.

Count Formula:

This formula is used for counting purpose. There are 5 count formula available.

• =COUNT(C2:C6) – This only count Integer/numbers in given range.
• =COUNTA(C2:C6) – Count all cell number content any value.
• =COUNTBLANK(C2:C6) – Search how many bank cell in given range

Couintif & Countifs Formula:

COUNTIF formula is used to count cells that contains some given condition (Ex- Star) as per below example. COUNTIFS formula count cells depend upon more conditions. (ex- how many stars from kolkata)

• =COUNTIF(C2:C7, “Star”)
• =COUNTIFS(B3:B8,”Star”, A3:A8, “Kolkata”)

OR Operator Formula:

Select cell > =or(A2<=A3) [ Range of data cells & output is True of data]

Select cell > =or(A2=A3) [ Range of data cells & output is False of data]

Sqrt Formula:

Select the cell > =sqrt(A4) [ Range of data cells & output is square number of data]

Trim Formula:

This formula is used to remove extra spaces between words of a cell allow only one space.

• =TRIM(A1:D1)

Concatenate Formula:

This formula will join multiple cell data in one cell. Some examples-

• =CONCATENATE(B2&C2) | =CONCATENATE(B2,C2)
• =CONCATENATE(B2, ” “,C2) Add space ( space under quote) between two cell data.

Percentage Calculation:

Calculate 15% value of 850.

IF Function:

Example-1: Specify bonus, If salary Under 20000- MOBILE, If salary more than 20000- LAPTOP.

Example-2: Calculate Grade of mark sheet- TOTAL: 400=A, 300=B, 200=C, below 199=FAIL.

• =IF(C4>=400,”A”,  IF(C4>=300,”B”,  IF(C4>=200,”C”,”FAIL”)))

## Even Odd number

In decimal number we can rounds nearest even or odd by using this command. =EVEN rounds nearest number & = ODD rounds nearest odd number.

Formula used-

=EVEN

=ODD

### 2 thoughts on “Microsoft Excel basic formulas”

1. 2. 