EXCEL - 3
OVERVIEW
FORMULA & FUNCTION
FORMULA is an equation which we create for our calculation.
FUNCTION predefined formula.
EXCEL :-
If you are not getting the correct answer from the formula, then try to use this evaluate you formula with the help of Evaluate Formula in Formulas Tab.
Google sheets :-
IN Google sheets there is not a particular option you can see.
12 categories in Excel function.
So, We will only learn only those which are important for DATA ANALYSIS ( Specific Domain).
General :-
SUM
AVERAGE
COUNT
IF
VLOOKUP
HLOOKUP
INDEX and MATCH
SUMIF and SUMIFS
COUNTIF and COUNTIFS
IFERROR
CONCATENATE or CONCAT
TEXT
LEFT, RIGHT, MID
DATEDIF
Data Cleaning formula :-
TRIM
PROPER
LOWER and UPPER
CLEAN
SUBSTITUTE
LEFT, RIGHT, MID
LEN
CLEAN and TRIM Combination
IF and ISNUMBER
IF and ISTEXT
IF and ISBLANK
TEXT and DATEVALUE
DATA Manipulation :-
CONCATENATE or CONCAT
LEFT, RIGHT, MID
LEN
FIND and SEARCH
SUBSTITUTE
IF
IFERROR
TEXT
CONVERT
TRANSPOSE
SUMIF and SUMIFS
INDEX and MATCH
VLOOKUP
IF, ISNUMBER, and SEARCH Combination
DATE and TIME Functions
CONVERT Function
RANK
ROUND
TEXTJOIN
IF, ISBLANK, and IFERROR Combination
Statistical Based Formula :-
AVERAGE
MEDIAN
MODE
STDEV.P and STDEV.S
VAR.P and VAR.S
CORREL
COVAR
RANK.EQ and RANK.AVG
PERCENTILE.INC and PERCENTILE.EXC
QUARTILE.INC and QUARTILE.EXC
Z.TEST
T.TEST
F.TEST
NORM.S.DIST and NORM.DIST
NORM.INV
EXCEL :-
If you don’t know the name of the option, go to INSERT Function option and inside a box write the description of the formula you will get the name of the formula.
Google sheets :-
IN Google sheets there is not a particular option you can see.
Function Explain :-
Step 1 :-
Whenever writing a formula, it starts with “ = ”.
Step 2 :-
Write formula names like sum, max etc.
Step3 :-
Open Bracket () which takes input as per the formula.
In this whenever you are writing a formula you will get suggestions.
In which if you see [ ] square brackets it means that the parameter is optional to write.
Other parameters are compulsory to give to get the desired output.
Example : -
= sum(value1,value2… - [optional])
LEARNING IS AN EVERYDAY PROCESS SO, i will be updating previous learning with new tips and tricks.
Follow me on this where every day will be added if i learn something new about it :- https://dev.to/nitinbhatt46
THANK YOU
Top comments (0)