There are lots of formulas in excel. If you are willing to get great command in excel then you need to start learning from very basic to advance in a scientific pattern. In this learning session you will learn most used excel command. First understand what is function and formula, these two terms are used frequently. They are closely related, but not same.
What is formula in excel?
In excel, Formula is some instruction which we can run in excel cell. Formula always start with “=” operator like [ =a1+b1 +c1] it will addition values of all specified cells.
What is function in excel?
In excel, function has some special name and purpose. like [ =sum(*:*) or =Max etc. ]
What is function arguments?
Most function require input to return output. This input in excel is called argument.
20 Most essential formula in excel
- Sum Formula in excel
Sum formula is used for Addition purpose. it can aggregates values in given cell range of row or column.
Example of sum formula:
=SUM(A1:A8) – This will sums the values of a column.
=SUM(A11:E11) – This formula will sums the values of selected rows.
=SUM(C1:C3,C5,C7:C8) – It will sums values from range C1 to C3, skips C4, adds C7, then add C7 and C8.
=SUM(E1:E6)/2 – Turn your function into a formula. Divide your addition result.
=SUMIF() -This SUMIF formula add cell values upon specific condition.
=SUMIFS() – In excel, SUMIFS function sum cell values depend upon multiple condition match.
Average Formula:
In excel, Average function calculate the average value of given numbers present in cells.
=Average(C3:E3)
=AVERAGE(A1:A5,A8,A12:A15) – Average with mixed arguments. it will provide average value of periodic selected cells only.
=AVERAGEIFS(C5:C14,D5:D14,”Kolkata”) -It will only average of numbers from kolkata.
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
For Students Practice: OPEN SPREADSHEET
