Formulas Cheat Sheet

1. DAILY DOSE: FORMULA'S CHEAT SHEET to helpD-to-D

sum;
=SUM (Value, valuez, values, valued)

Eg: =sum (1,2, 3, 4 Result: 10

ir

IF (condition, “DISPLAY this condition s true”, DISPLAY
his condition is false")

gi if (20°15, “You ae true”, "You are false"); Result: You
are false

COUNT:
=COUNT (value in AL, value in AZ, value in A3)
Es: =eount (1,2, 3); Result:3

Max:
=MAX (range of cell)
Eg: max (1,2, 3}; Result: 3

MIN:
=MIN (value in AL, value in A2, value in A3)
Eg: =min(1,2, 3) Result: 1

AVERAGE:
AVERAGE (range of cell)
Eg: =average (1,2, 3); Result: 2

VLOOKUP:
=VLOOKUP (find his value, in this range, from this column,
is column sorted?)

Eg: eviookup ("ABHI", SAS1:5854, 2,0); Result: 85
Main

Name | Markein

Works

Mir

CONCATENATE

Eg: =concatenatel“dr excel is introduced BJ",
“Bhardwaj” Result: dr. excel is introduced by Bharduwaj

IFERROR.
ERROR (value, replace with this value Is an error
Eg: =error(03, 1); Result: (hyphen if error) else D3

Points to be notified:
+ InSUMfunction, we can use only 255 values

To crack the limitation convertvaluesto a range.
Eg: Sum (A1:AA 61:84, C1:C4)
‘Always keep text between parenthesis{*)in the
formulas.
We canalso use “8 symbolfor concatenathg two
Calis eg: = "Call moon 8" "8"+91-94830.06767
‘est: Call me on [phone]

FORMULA ERRORS:

NAME? | Name wrongly spelled in formula / cell
N/A | Not Applicable, Value you need is nat there
4NUMI_ | Ifthe number I too big to display

[WDIV/01_| Cell value divided by 0 7empty cell
NULL

Intersection oftwo ranges doesn't intersect
Celi reference is deleted

y

dust thecolumn with

HVALUET | cel refers to an argument or operand of
the wrong type.

toner $ symbol

FORMULAS WHEN DRAGGED

ano] se “| mA E
ROMEO | se ® 75 ®

HLOOKUP:
—HLOOKUP (find this value, in this range from this row is
range sorted?)

Es: shlookup ("Marks in Power Pivot, SA$1:$8$4, 3,0)

Result: 98

‘When Dragged
Downside | Rightide
Ar [az rm
ASI (lockrow) [AST BST
SAL (lock colu) | $AZ EN
SASI remains same because & Care locked

Call Reference

dwaj/ & Facebook @vibhat 9487