Excel Formulas & What to be Aware of
FINAL FILE
Link Topic Comprehension
Calc How Excel Calculates - Order of Precedence Select One…
Formulas Simple but Useful Formulas Select One…
Rule Excel's Essential Rule Select One…
Referencing Absolute and Relative Referencing Select One…
Names Range Names for Clarity Select One…
OtherSheets Referencing other Workbooks or Worksheets Select One…
CircularRef Circular References - How to Locate & Correct Select One…
Them
CombineCells Combine Values from 2 or More Cells to 1 Cell Select One…
Challenge Challenge: Allocate Yearly Value to Months Select One…
Based on Percentages
Your Notes
How Excel Calculates - Order of Precedence
2 3
1 1
Data input used in the
=(2+1)*(3+1) 12 following examples
=(2+1)*3+1 10
=2+1*SUM(C3:C4) 6
=(B3+B4)*SUM(C3:C4) 12
=(B3+B4)+SUM(C3:C4)*2+1 12
=((B3+B4)+SUM(C3:C4))*2+1 15
=(B3+B4+SUM(C3:C4))*2+1 15
=(B3+B4+SUM(C3:C4))*(2+1) 21
=(SUM(B3:C4))*(2+1) 21
=-SUM(C3:C4)*B3 -8
=SUM(B3:B4)=SUM(C3:C4) FALSE
=SUM(B3:B4)>SUM(C3:C4) FALSE
=SUM(B3:B4)<SUM(C3:C4) TRUE
=SUM(B3:B4)+1>=SUM(C3:C4) TRUE
=FALSE()+1 1 It's good to know this if you're planning to
=FALSE()*1 0 become Advanced in Excel. If any mathematical
=TRUE()+1 2 operations are applied to TRUE & FALSE values,
TRUE becomes 1 and FALSE becomes 0.
=TRUE()*1 1
Simple but Useful Formulas
Value after % Increase or Decrease
Item Price Discount Final Value Final Value
Scarf-M 60 10% 54 54
Scarf-W 80 20% 64 64
Shirt-W 50 15% 42.5 42.5
Shirt-M 40 10% 36 36
Monthly & Year-to-Date Calculations
Profit Year-to-Date in USD
Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19
WenCaL 2200 3200 4400 5400 6800 7600 8900
Blend 14900 27400 49000 64100 82300 100300 116400
Voltage 4700 10400 18400 27400 34600 43200 52400
Inkly 11900 13900 24500 35000 41000 49900 54700
Sleops 14600 30700 43300 56000 66900 78000 88400
Kind Ape 4200 6300 12800 14200 21400 31600 41900
Pet Feed 6900 19400 40100 66800 86400 108300 136300
Right App 1700 2900 4400 4500 4800 5500 5800
Mirrrr 48000 66300 100700 140000 186400 234000 284500
Halotot 4600 10700 19000 26300 30800 34900 37700
Flowrrr 8800 15100 20000 22600 23200 23300 23500
Silvrr 12000 13400 15600 17600 19800 21200 23400
Profit per Month in USD
Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19
WenCaL 2200 1000 1200 1000 1400 800 1300
Blend 14900 12500 21600 15100 18200 18000 16100
Voltage 4700 5700 8000 9000 7200 8600 9200
Inkly 11900 2000 10600 10500 6000 8900 4800
Sleops 14600 16100 12600 12700 10900 11100 10400
Kind Ape 4200 2100 6500 1400 7200 10200 10300
Pet Feed 6900 12500 20700 26700 19600 21900 28000
Right App 1700 1200 1500 100 300 700 300
Mirrrr 48000 18300 34400 39300 46400 47600 50500
Halotot 4600 6100 8300 7300 4500 4100 2800
Flowrrr 8800 6300 4900 2600 600 100 200
Silvrr 12000 1400 2200 2000 2200 1400 2200
in USD
Aug-19 Sep-19 Oct-19 Nov-19 Dec-19
11000 11300 19100 27600 28800
127200 139500 153800 171200 175000
62600 71200 78200 90600 91600
57400 60400 60500 62700 72800
126300 126300 126300 126500 127500
49100 57600 64400 65000 74900
136400 148300 172000 190900 191300
8100 8700 18400 24900 34400
330100 366200 402600 402700 403200
51600 56600 59200 59300 59500
25700 28000 32200 32600 32800
26600 27900 36000 38800 45000
n USD
Aug-19 Sep-19 Oct-19 Nov-19 Dec-19
2100 300 7800 8500 1200
10800 12300 14300 17400 3800
10200 8600 7000 12400 1000 Good practice is to keep all formulas in
2700 3000 100 2200 10100 a range consistent if possible. For this
case, If we wanted to keep Jan to Dec
37900 0 0 200 1000 formulas the same, we could use
7200 8500 6800 600 9900 Excel's IF function which we learn in
100 11900 23700 18900 400 the next section.
2300 600 9700 6500 9500
45600 36100 36400 100 500
13900 5000 2600 100 200
2200 2300 4200 400 200
3200 1300 8100 2800 6200
formulas in
e. For this
an to Dec
d use
learn in
Excel's Essential Rule
Service 1500 Don't use constants
inside formulas.
Total Charges 1800 =B4*(1+20%)
Service 1500
VAT 20%
Total Charges 1800 =B7*(1+B8) Keep them in cells with
labels. Unless they are
universal (12 months in a
year, 24 hours in a day….)
Item Jan Feb % Change
Scarf-M 1200 1200 0%
Scarf-W 1600 1440 -10%
Shirt-W 1000 1450 45% Make sure a formula is
Shirt-M 800 920 15% consistent for a range of
cells. Do not remove
Belt-M New 200 formulas if they result in an
Belt-W 400 500 25% error. Instead use error
handling (covered in next
section).
Item Jan Feb % Change
Scarf-M 1200 1200 0%
We cover this in
Scarf-W 1600 1440 -10% the next section.
Shirt-W 1000 1450 45%
Shirt-M 800 920 15%
Belt-M New 200
Belt-W 400 500 25%
Absolute and Relative Referencing
Global Discount 10% Global Discount
Item Price Discount Jan Price Item Jan Price
Scarf-M 60 10% 54 Scarf-M 54
Scarf-W 80 10% 72 Scarf-W 72
Shirt-W 50 10% 45 Shirt-W 45
Shirt-M 40 10% 36 Shirt-M 36
This is our price list per
item. Let's calculate
different discount
scenarios.
Global Discount 10% Monthly Discount 50% 10%
Item Jan Price Feb Price Item Jan Price Feb Price
Scarf-M 54 54 Scarf-M 30 54
Scarf-W 72 72 Scarf-W 40 72
Shirt-W 45 45 Shirt-W 25 45
Shirt-M 36 36 Shirt-M 20 36
Range Names for Clarity
Global Discount 10%
Item Price Item Jan Price
Scarf-M 60 Scarf-M 54
Scarf-W 80 Scarf-W 72
Shirt-W 50 Shirt-W 45
Shirt-M 40 Shirt-M 36
Use the AVERAGE Function (More
info on Excel functions is covered
Average Price 57.5 in the next section.)
57.5
Referencing other Workbooks or Worksheets
With Global Discount
Item Price Item Jan Price Use global discount
Scarf-M 60 Scarf-M 54 from sheet
"Referencing" cell E3.
Scarf-W 80 Scarf-W 72
Shirt-W 50 Shirt-W 45
Shirt-M 40 Shirt-M 36
You can also reference cells from
other workbooks. Open the other
workbook and switch to it when
writing the formula.
Circular References - How to Locate & Correct Them
Scarf-M 1200
Shirt-M 200
Total Men 1400
Circular reference occurs if
the cell result is part of the
Scarf-W 1600 input as well.
Shirt-W 1000
Total Women 2600
TRY THIS - Change the formula to
=SUM(B7:B9). This will trigger a
circular reference.
Combine Values from 2 or More Cells to 1 Cell
Item Name Type (Men/Women) Combine Combine with space
Scarf M ScarfM Scarf M
Scarf W ScarfW Scarf W
Shirt W ShirtW Shirt W
Shirt M ShirtM Shirt M
Select Year 2020
Price Volume Profit
Item Price-2020 Volume-2020 Profit-2020
Scarf-M
Scarf-W
Shirt-W
Shirt-M
Combine with symbol
Scarf-M
Scarf-W The ampersand allows you to
Shirt-W join the values from different
Shirt-M cells to one cell.
Challenge: Allocate Yearly Value to Months Based on Percentages
1 Allocate the total profit estimated for all Apps to months based on the percentages
2 Double-check results: Ensure the total value equals yearly profit (add a check in cell P26)
Allocate this total value based on the
Total yearly profit 1,500,000 monthly percentages & percentage
distribution by app.
10.00% 5.00% 12.00% 10.00% 10.00%
App Jan Feb Mar Apr May
2.00% WenCaL 3,000 1,500 3,600 3,000 3,000
13.00% Blend 19,500 9,750 23,400 19,500 19,500
7.00% Voltage 10,500 5,250 12,600 10,500 10,500
5.00% Inkly 7,500 3,750 9,000 7,500 7,500
10.00% Sleops 15,000 7,500 18,000 15,000 15,000
6.00% Kind Ape 9,000 4,500 10,800 9,000 9,000
14.00% Pet Feed 21,000 10,500 25,200 21,000 21,000
4.00% Right App 6,000 3,000 7,200 6,000 6,000
30.00% Mirrrr 45,000 22,500 54,000 45,000 45,000
4.00% Halotot 6,000 3,000 7,200 6,000 6,000
2.00% Flowrrr 3,000 1,500 3,600 3,000 3,000
3.00% Silvrr 4,500 2,250 5,400 4,500 4,500
100.00% 150,000 75,000 180,000 150,000 150,000
TRUE
TRUE
✘ 1. Done
✘ 2. Done
WELL DONE!
ed on the
centage
12.00% 8.00% 5.00% 8.00% 2.00% 4.00% 14.00%
Jun Jul Aug Sep Oct Nov Dec
3,600 2,400 1,500 2,400 600 1,200 4,200
23,400 15,600 9,750 15,600 3,900 7,800 27,300
12,600 8,400 5,250 8,400 2,100 4,200 14,700
9,000 6,000 3,750 6,000 1,500 3,000 10,500
18,000 12,000 7,500 12,000 3,000 6,000 21,000
10,800 7,200 4,500 7,200 1,800 3,600 12,600
25,200 16,800 10,500 16,800 4,200 8,400 29,400
7,200 4,800 3,000 4,800 1,200 2,400 8,400
54,000 36,000 22,500 36,000 9,000 18,000 63,000
7,200 4,800 3,000 4,800 1,200 2,400 8,400
3,600 2,400 1,500 2,400 600 1,200 4,200
5,400 3,600 2,250 3,600 900 1,800 6,300
180,000 120,000 75,000 120,000 30,000 60,000 210,000
100.00%
