In this module, we will focus on learning how to make basic arithmetic operations using excel
Use the following guidelines to calculate the statements below:
= equals, use = sign before the formula to calculate a formula
+ plus
- minus
/ divide
* multiply
% percentage sign (will divide the number by 100 if added after a value)
Arithmertics
2 Plus 3 5
3 Minus 1 2
5 Times 10 50
10 Divided by 2 5
Percentages using division of numbers
10 Out of 100 10.00%
3 Out of 6 50.00%
1.5 Out of 1 150.00%
Calculate percentage of change
Stock Price 2015 Price 2016 Year over Year % change
Stock A 100 150
Stock B 100 50
In this module, we will focus on learning how to make basic arithmetic operations using Excel
use the following guidelines to calculate the statements below:
= equals, use = sign before the formula to calculate a formula
+ plus
- minus
/ divide
* multiply
% percentage sign (will divide the number by 100 if added after a value)
Arithmertics Answer resAnswer formula
2 Plus 3 5 5
3 Minus 1 2 2
5 Times 10 50 50
10 Divided by 2 5 5
Percentages
Part A - Percentages using division of numbers Answer resAnswer formula
10 Out of 100 10% 0.1
3 Out of 6 50% 0.5
1.5 Out of 1 150% 1.5
Part B - Calculate percentage change
Stock Price 2015 Price 2016 change in %change in % - Option B
Stock A 100 150 50% 50%
Stock B 100 50 -50% -50%
Part A - Percentages using division of numbers
Excel can easily help us with calculating and presenting numbers
The first question is - how much is 10 out of 100 in percentages:
First, we will use division to calculate the number as a decimal fraction
Then we will use Excel's formatting to get a percentages view
Question 1:
Step 1:
divide 100 by 10 in the following way:
0.1
With a default formatting setting, the output would look like this:
0.1
Step 2
Now let's convert the decimal number to percentage
Select the Percentage formatting in the "Home" tab:
Now we get the following output:
10%
Question 2:
Step 1:
Divide 3 by 6
0.5
With the default formatting, the output would look like this:
0.5
Step 2
Now let's convert the decimal number to percentage
now we get the following output
50%
Question 3:
Step 1:
divide 1.5 by 1 (yes, this is not a mistake - we can divide numbers by a lower value - however, we would get a percentage num
1.5
Step 2:
Format as percentage:
150%
Part B - Calculate percentage change
To Calculate the percentage change between two numbers we would use the following formula
Option 1:
Number Period 2
-1
Number Period 1
That means - Divide period 2 by period 1, and substract 1
In example 1, stock price of 2015 - $100, 2016 - $150. That means that it grew by 50%
The formual should look like this:
0.5
Option 2:
Alternatively, we can apply the following formula to calculate percentage differences:
Number Period 2 - Number Period 1
Number Period 1
The formula will look like this:
0.5
, we would get a percentage number greater than 150%)
Name No. of Apples
Guy 3
Lev 5
Yoav 2
Yossi 6
Dana 8
Total 24
Maximum 8
Minimum 2
Please calculate how many items are in the table:
Chili sauce
BBQ sauce
Vinaigrette sauce
Wine sauce
Mint sauce
Steak sauce
Count A 6
Count
Exercise 1 Table A contains names and their respective grades for Excel 101 Course
Complete column C using only IF formula
Grade 60 or higher = Pass
Grade less than 60 = Fail
Name Grade Pass/Fail
Adi 98 PASS
Beni 55 FAIL
Charlie 15 FAIL
Dani 60 PASS
Exercise 2 The following table is an extract from an accounting system that contains four journal entries
Check if column A's cells match column B's cell
if they match - return "match", otherwise return "no match"
A B
Debit Credit
Journal Entry 1 $94.00 $94.00
Journal Entry 2 $109.00 $109.00
Journal Entry 3 $85.00 $85.50
Journal Entry 4 $12.00 $12.00
The table below contains details of high school students names and ages, use IF formu
1 If the student's age is 16 or above, he/she is eligible for a driver's license. Check if the
2 If the student is younger than 18 years old he/she is a minor. Check whether the stud
Exercise 3 Number Name
1 Arik
2 Ben
3 Cermit
4 Dan
5 Eliko
6 Fage
7 George
8 Herzl
Exercise 4 An A+ student gets 100% scholarship and non A+ gets 50% scholarship as shown in the table below:
Amount
A+ 100%
A- 50%
The following table contains the names of students from 2024 class.
Use IF function to calculate the scholarships' amounts each of them will get
Name GPA
Sam A+ 100%
Ari A- 50%
Xena A- 50%
Gabe A+ 100%
Eliko A- 50%
Daniela A+ 100%
Rotem A- 50%
r Excel 101 Course
stem that contains four journal entries
Same value?
Match
Match
NO Match
Match
f high school students names and ages, use IF formula to complete columns D and E
, he/she is eligible for a driver's license. Check if they are eligible or not. Answer in column D
years old he/she is a minor. Check whether the student is a minor or not. for Minor return "Minor" and non minor = "Adult" anwswe
Age Driver Licence Minor/Adult?
16 Minor
18 Adult
15.5 Minor
19 Adult
18 Adult
13 Minor
18 Adult
17 Minor
50% scholarship as shown in the table below:
m 2024 class.
each of them will get
non minor = "Adult" anwswer in column E
Exercie 1 Table A contains names and their respective grades for Excel 101 Course
Complete column C using only IF formula
Grade 60 or higher = Pass
Grade less than 60 = Fail
Name Grade Pass/Fail
Adi 98 Pass Pass
Beni 55 Fail Fail
Charlie 15 Fail Fail
Dani 60 Pass Pass
IF formula has three main parts - let's see how it applies to the first row in this exercise .
Argument This example - entereArgument Explained
Logical test B7>=60 is the value in B7 (98) equals or greater than 60?
Value if True "Pass" if 98 is greater or equals to 60 return the word pa
Value if False "Fail" if 98 is NOT greater or equals to 60 return the wo
As 98 is greater than 60, the result is "Pass"
The following table is an extract from an accounting system that contai
Check if column A's cells match column B's cell
if match - return "match", otherwise return "no match"
A B
Exercise 2 Debit Credit
Journal Entry 1 $94.00 $94.00
Journal Entry 2 $109.00 $109.00
Journal Entry 3 $85.00 $85.50
Journal Entry 4 $12.00 $12.00
Argument This examplArgument Explained
1 Logical test C7=D7 Are the values in C7 and D7 the same
2 Value if True "match" if the debit and credit values are the s
3 Value if False "not matchif the debit and credit values are the s
Exercise 3 The table below contains details of high school students names and ages, use IF formula to co
If the student's age is 16 or above, he/she is eligible for a driver's license. Check if they are e
If the student is younger than 18 years old he/she is a minor. Check whether the student is
Column D
Number Name Age Driver Licence
1 Arik 16 Yes
2 Ben 18 Yes
3 Cermit 15.5 No
4 Dan 19 Yes
5 Eliko 18 Yes
6 Fage 13 No
7 George 18 Yes
8 Herzl 17 Yes
Check 1 - driver licence eligibility (column D)
This example -
Argument entered value Argument Explained
Logical test D9>=16 Is D9 greater or equal to 16?
Value if True "Yes" If greater or equals return "Yes"
Value if False "No" If NOT greater or equals return "No"
As 16 is equal to 16, the answer that will be return is "Yes"
check 2 - minor/adult (column E)
This example -
Argument entered value Argument Explained
Logical test D9<18 is D9 lower than 18?
Value if True "Minor" if lower than 18 return "Minor"
Value if False "Adult" if NOT lower than 18, return "Adult
Note: both tests can be applied in the opposite way (i.e - Check if the
student is younger than 16, and if he's 18 or older). If you applied the
formula correctly, the results would be the same. If this is your first
encounter with IF formula, we recommend following the exact
instructions in the exercise.
Exercise 4 An A+ student gets 100% scholarship and non A+ gets 50% scholarship as shown in the table
Amount
A+ 100%
A- 50%
The following table contains the names of students from 2024 class.
Use IF function to calculate the scholarships' amounts each of them will get
Answer without cells references
Name GPA Tuition Scholarship
Sam A+ 46,866 46,866
Ari A- 33,495 16,748
Xena A- 35,087 17,544
Gabe A+ 42,603 42,603
Eliko A- 36,971 18,486
Daniela A+ 41,286 41,286
Rotem A- 37,732 18,866
This example -
Argument entered value Argument Explained
Logical test B11=A+ we check if the text in cell B11 equals to the text
Value if True 100% if the arguement is true - return 100%.
Value if False 50% If false - means you're A- student (and not A+), th
Important: when adding Text in a formula - always use quotation marks ("")
Tip - drag the formula of the first cell downwards, instead of retyping the formula each time!
he first row in this exercise .
(98) equals or greater than 60?
equals to 60 return the word pass
er or equals to 60 return the word fail
n accounting system that contains four journal entries
urn "no match"
Same value?
match match
match match
no match match
match match
ent Explained
e values in C7 and D7 the same (equal to 94)?
ebit and credit values are the same return the word match
ebit and credit values are the same return the text "no match."
s and ages, use IF formula to complete columns D and E
ver's license. Check if they are eligible or not. Answer in column D
Check whether the student is a minor or not. for Minor return "Minor" and non minor = "Adult" anwswer in column E
Column E
Minor/Adult?
Yes Minor Adult
Yes Adult Adult
No Minor Minor
No Adult Minor
No Adult Minor
No Minor Minor
No Adult Minor
Yes Minor Adult
ls return "Yes"
equals return "No"
eturn "Minor"
18, return "Adult
olarship as shown in the table below:
them will get
r without cells references
xt in cell B11 equals to the text A+
s true - return 100%.
ou're A- student (and not A+), then you get 50%.
Make Model Color Requests
Lincoln Town Car Yellow 2 FALSE
Hummer H1 Purple 1 FALSE
Toyota RAV4 Blue 10 TRUE
Ford Galaxie Yellow 6 FALSE
Honda Odyssey Yellow 3 FALSE
Maybach 57 White 8 FALSE
Mercedes-Benz S-Class Black 6 FALSE
Mercedes-Benz SLK-Class Green 10 FALSE
Dodge Challenger Blue 4 FALSE
Mitsubishi Galant Blue 1 FALSE
Buick Coachbuilder Yellow 8 FALSE
Lincoln Town Car Purple 4 FALSE
Land Rover Discovery White 8 FALSE
Dodge Ram Van 2500 Black 10 FALSE
Toyota MR2 Purple 5 FALSE
Toyota Land Cruiser Blue 1 FALSE
Land Rover Defender Ice EdiRed 6 FALSE
Mazda B-Series Plus Yellow 7 FALSE
Rolls-Royce Ghost Black 9 FALSE
Chevrolet Silverado 3500 Blue 8 TRUE
Ford Crown Victoria Purple 10 FALSE
Ford Focus White 10 FALSE
Mercury Tracer Purple 10 FALSE
Dodge Intrepid Red 10 FALSE
Mazda Mazda5 Green 2 FALSE
Toyota Avalon Purple 9 FALSE
BMW 6 Series Orange 10 FALSE
Ford Tempo Blue 6 FALSE
Volkswagen Jetta Yellow 2 FALSE
Chevrolet Astro White 2 FALSE
Ford Expedition Red 10 FALSE
Isuzu Ascender Green 2 FALSE
BMW Z4 M Purple 8 FALSE
Lincoln Town Car Purple 4 FALSE
Isuzu Trooper Blue 1 FALSE
Dodge Ram Van 2500 Orange 10 FALSE
Lexus LX Yellow 10 FALSE
Toyota Yaris Yellow 6 FALSE
Toyota MR2 Red 9 FALSE
Ford Mustang Red 3 FALSE
GMC Suburban 2500 White 9 FALSE
Chevrolet Corvette Red 1 FALSE
Suzuki Aerio Yellow 10 FALSE
Acura RL Blue 9 TRUE
Mazda Mazda6 Green 1 FALSE
Infiniti QX Green 1 FALSE
Acura Legend Blue 6 FALSE
Subaru Tribeca White 1 FALSE
Chevrolet Astro Yellow 1 FALSE
Ford ZX2 Black 6 FALSE
Q1.
Q2.
Q3.
Q4.
See which rows contain more than 6 requested blue cars .
How many people have requested Yellow Lincoln car.
If true display "Yellow lincoln" and if atleast one false display" Not Yellow Lincoln."
How would you write a formula that returns TRUE if the car is either a Ford or blue?
Let’s write a function that returns “Red or Blue” for any vehicle with a listed color of red or blue.
true,false
ellow Lincoln."
