Notes
VALUE 1 VALUE 2 TOTAL - Formula is a calculation ...
5 2 3 - consists of 2 things :
i) INPUT
7 7
ii)OPERATOR
- INPUTS :
i) Value ( NUMBERS & TEXT)
ii) CR (the name of the that you want to use it's
value - CELL REFERENCE)
- OPERATOR
i) Arithmethic
ii) Logical
- Method to write our Formula :
i) Value & CR
ii) Named Ranges
iii) Excel Table
ion ...
TEXT)
e that you want to use it's
CE)
Formula :
List of operators
ARITHMETIC OPERATORS
Let's try it out… SEE THE DIFFERENCES
ARITHMETHIC OPERATOR
Value1 Value2 Value1 + Value2 Value1 * Value2
6 30 36 180
9 54 63 486
44 92 136 4048
LOGICAL OPERATOR
Value1 Value2 Value1 = Value2 Value1 <> Value2
51 51 TRUE 0
65 68 FALSE 1
96 30 FALSE 1
Value1 Value2 Value1 * ((Value1 + Value2) ^ 2) + Value2
6 30 7,806.00
9 54 35,775.00
44 92 813,916.00
51 51 530,655.00
65 68 1,149,853.00
96 30 1,524,126.00
37 7 71,639.00
Cell References
Practise 1
Relative Reference
FORMULA : ( 1 - Discount ) * Price * Quantity
Price Qty Discount Sales after Discount
6 30 40% 108.00
9 54 90% 48.60
44 92 80% 809.60
39 51 80% 397.80
65 68 30% 3094.00
96 30 40% 1728.00
37 7 30% 181.30
NOTES :
NOTES :
Absolute Cell Referen
Autofill
- When you have 2 D
- Excel will copy everything inside your
$H $68
formula :
You are locking both
i) Value
ROW and COLUMN
ii) Operator
-Shortcut key : F4
EXCEPT ...Cell Reference
Relative Reference
- When you have 1 D
Your CR will move according to where you
$H 68 - Lock the COL
drag it.
CANNOT DRAG RIGH
-----------------------------
H $68 - Lock the ROW
CANNOT DRAG UP or
EXCEPT ...Cell Reference
Relative Reference
- When you have 1 D
Your CR will move according to where you
$H 68 - Lock the COL
drag it.
CANNOT DRAG RIGH
-----------------------------
H $68 - Lock the ROW
CANNOT DRAG UP or
LOGICAL OPERATORS
METHIC OPERATOR NOTES :
Value1 ^ 2 Autofill
- How to execute Autofill :
36.00 i) Drag down
81.00 ii) Double Click
iii) Highlight all of the cell that you want
1,936.00 to fill in ( including your formula ) -> Ctrl
D
CAL OPERATOR
Value1 > Value2
0.00
0.00
1.00
NOTES : Notes
BODMAS : B: Bracket () , O: Order, D: Divide, M: Multiply , A: Evaluate Formula
Add, S: Subtract - used to see how excel calculate yo
- where can we find it ?
- Go to Formulas Tab -> Formula Au
Evaluate Formula
Add, S: Subtract - used to see how excel calculate yo
- where can we find it ?
- Go to Formulas Tab -> Formula Au
Evaluate Formula
Practise 2
Use F4 lock function (So that it does not
Absolute Reference flow to other cells when formatting
Discount 10%
Price Qty Sales after Discount
6 30 162
9 54 437.4
44 92 3643.2
39 51 1790.1
65 68 3978
96 30 2592
37 7 233.1
NOTES :
Absolute Cell Reference
- When you have 2 Dollar Sign
$H $68
You are locking both the
ROW and COLUMN
-Shortcut key : F4
- When you have 1 Dollar Sign
$H 68 - Lock the COLUMN
CANNOT DRAG RIGHT or LEFT
----------------------------------------
H $68 - Lock the ROW
CANNOT DRAG UP or DOWN
- When you have 1 Dollar Sign
$H 68 - Lock the COLUMN
CANNOT DRAG RIGHT or LEFT
----------------------------------------
H $68 - Lock the ROW
CANNOT DRAG UP or DOWN
Notes
Arithmetic VS Logical
1. Purpose of using it
- Arithmethic - To CALCULATE
- Logical - To COMPARE
2. The symbol used
3. The result/OUTPUT obtain
- Arithmethic :
- Logical :
l:
ell that you want
formula ) -> Ctrl
e Formula
o see how excel calculate your formula
can we find it ?
ormulas Tab -> Formula Auditing Group ->
e Formula
o see how excel calculate your formula
can we find it ?
ormulas Tab -> Formula Auditing Group ->
e Formula
Practise 3
Mixed Reference TIPS = Use 1 (100%) & Dollar Sign Only (To Lock Cell or Row)
Sales less the following Discounts
Price Qty 5% 10% 25% 50%
6 30 171 162 135 90
9 54 461.7 437.4 364.5 243
44 92 3845.6 3643.2 3036 2024
39 51 1889.55 1790.1 1491.75 994.5
65 68 4199 3978 3315 2210
96 30 2736 2592 2160 1440
37 7 246.05 233.1 194.25 129.5
Once you have done 1st cell N70, select all the row & column
Note: you want to formularized, Press Control Enter.
NOTES :
Alternative Method
Step 1 :
Highlight all of the cell that you want to
fill in including your Formula
Step 2 :
ell or Row) Press F2
Step 3 :
Press Ctrl Enter
SHOW FORMULA
- Show the formula instead of the value
Go to Formula tab --> Formula Auditing --> Show Formula icon
TRACE PRECEDENT
- It will tell you which cell/ What value is used to get this answer
- Can only work on a cell that contain formula
Click cell you want --> Go to Formula tab --> Formula Auditing --> Click
Trace Precedents
It will allow you to check how the formula is derived
TRACE DEPENDENT
- it will tell you that this input is used in what formula?
- INPUT and Formula works.
Named ranges make formulas easier to read, faster to
develop, and more portable. They're also useful for da
validation, hyperlinks, and dynamic ranges.
to read, faster to EXAMPLE 1
also useful for data Sales
ranges. $ 156,588.48
$ 147,587.48
$ 145,698.25
Use Range for easier calculations.
EXAMPLE 2
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
Note:
Method 2
Summary
Q1
Q2
Q3
Q4
NOTES :
- Named Ranges is a feature/too
Expenses Profit cell/range
$ 133,874.12 22,714.36 - Named ranges make formulas
i) easier to read,
$ 12,574.48 135,013.00 ii) faster to develop, and more p
iii) useful for data validation, hyp
$ 95,698.57 49,999.68
r easier calculations. - RULES THAT YOU NEED TO FOL
1. Cannot Start with number
2. Only "_" symbol can be used
3. No Space allowed
4. Cannot use the same name
Week 1 Week 2 Week 3 Week 4
518 164 741 910 #Name Error
293 910 939 362
273 331 851 741
277 146 202 398
316 380 985 362
422 854 954 114
715 577 761 615
338 350 128 751
309 591 265 905
268 971 193 497
725 469 242 465
679 251 981 832
Select all data in the table --> Formula --> Create From Selection
Total by Quarter
: 1 cell = cell
ed Ranges is a feature/tool that allows you to give a SPECIFIC NAME to a
nge multiple cell = range, array/vector
ed ranges make formulas
er to read,
er to develop, and more portable.
ful for data validation, hyperlinks, and dynamic ranges.
S THAT YOU NEED TO FOLLOW WHEN DEFINING NAME :
not Start with number
y "_" symbol can be used
Space allowed
not use the same name
e Error
= range, array/vector
Profit
22714.36
135013
49999.68
Region Salesperson Product Brand Unit Price
North Lee Bike BMX $ 199.95
South Hazelwood Skateboard Krown $ 79.95
East Bundy Snowboard Burton $ 119.95
West Nordstrom Skateboard Pumgo $ 99.95
South Lee Skateboard Pumgo $ 99.95
West Nordstrom Snowboard Nitro $ 139.95
North Bundy Bike Schwinn $ 169.95
East Lee Skateboard Sector 9 $ 69.95
East Nordstrom Snowboard K2 $ 129.95
North Bundy Bike Mongoose $ 189.95
West Lee Bike BMX $ 199.95
East Bundy Skateboard Pumgo $ 99.95
West Bundy Skateboard Sector 9 $ 69.95
North Nordstrom Snowboard K2 $ 129.95
West Nordstrom Bike Mongoose $ 189.95
South Lee Snowboard Nitro $ 139.95
East Hazelwood Skateboard Pumgo $ 99.95
West Hazelwood Skateboard Krown $ 79.95
East Lee Bike Schwinn $ 169.95
South Bundy Bike Mongoose $ 189.95
North Hazelwood Skateboard Krown $ 79.95
East Hazelwood Snowboard Nitro $ 139.95
East Bundy Bike BMX $ 199.95
East Nordstrom Skateboard Pumgo $ 99.95
South Bundy Skateboard Sector 9 $ 69.95
North Lee Bike Mongoose $ 189.95
South Nordstrom Snowboard Burton $ 119.95
West Bundy Snowboard Burton $ 119.95
North Bundy Skateboard Pumgo $ 99.95
North Lee Bike Schwinn $ 169.95
South Nordstrom Skateboard Sector 9 $ 69.95
East Hazelwood Bike BMX $ 199.95
South Bundy Snowboard Nitro $ 139.95
East Lee Bike BMX $ 199.95
West Nordstrom Skateboard Krown $ 79.95
East Hazelwood Snowboard K2 $ 129.95
South Lee Bike Schwinn $ 169.95
East Nordstrom Bike BMX $ 199.95
North Bundy Skateboard Krown $ 79.95
West Lee Skateboard Pumgo $ 99.95
East Nordstrom Snowboard Burton $ 119.95
South Nordstrom Snowboard K2 $ 129.95
West Nordstrom Snowboard K2 $ 129.95
West Hazelwood Skateboard Pumgo $ 99.95
West Lee Bike Schwinn $ 169.95
West Bundy Bike BMX $ 199.95
West Hazelwood Bike BMX $ 199.95
West Hazelwood Snowboard K2 $ 129.95
West Nordstrom Snowboard Burton $ 119.95
Discount % Net Price
10% $ 179.95 Methods to create a table in Excel
10% $ 71.96 1) Use Insert Tab >> Select Table
10% $ 107.96 2) Use Ctrl+T as keyboard Shortcut
15% $ 84.96 3) Use Home Tab >> Click format As Table
15% $ 84.96 ** user need to select a single cell within data range
15% $ 118.96 ** Method 3 allows user to choose table formatting before create
15% $ 144.46 4) Use "Quick Analysis" button [bottom right corner]
20% $ 55.96 ** user need to select all record [Ctrl + A]
20% $ 103.96
20% $ 151.96 Benefits:
10% $ 179.95 1) Auto freeze top row upon scroll down records
15% $ 84.96 2) Auto format for new columns & rows
20% $ 55.96 3) auto calculate for new columns & rows
20% $ 103.96 4) An interactive filter applicable [Slicer]
20% $ 151.96
15% $ 118.96 Slicer: Select data and click Slicer & click what is relevant
15% $ 84.96 Multi Select - To select more than 1 data.
10% $ 71.96
15% $ 144.46
20% $ 151.96
10% $ 71.96
15% $ 118.96
10% $ 179.95
15% $ 84.96
20% $ 55.96
20% $ 151.96
10% $ 107.96
10% $ 107.96
15% $ 84.96
15% $ 144.46
20% $ 55.96
10% $ 179.95
15% $ 118.96
10% $ 179.95
10% $ 71.96
20% $ 103.96
15% $ 144.46
10% $ 179.95
10% $ 71.96
15% $ 84.96
10% $ 107.96
20% $ 103.96
20% $ 103.96
15% $ 84.96
15% $ 144.46
10% $ 179.95
10% $ 179.95
20% $ 103.96
10% $ 107.96
format As Table
cell within data range
oose table formatting before create
n [bottom right corner]
croll down records
mns & rows
click Slicer & click what is relevant you want
select more than 1 data.
Product Revenue
Carlota 1144 TOTAL SALES :
Quad 338
Tri Fly 1033 Revenue
Bango 832 2500
Elon 1041 2000
Nathan 546 1500
James 798 1000
Jency 2034
500
0
Carlota Quad Tri Fly Bango Elon Nathan James
Create Excel table:
You can insert aditional data and
it will auto calculate
How to use Excel Table inside of Formula
1. Identify name of your Excel Table.
2. How to use the Excel table?
Total Sales : 7766
0
7766
Revenue
Bango Elon Nathan James Jency
