Assignments
from Cell references in excel
Assignments using Relative reference In Excel
Assignment 1: Basic Addition with Relative
References
Data:
A | B |
5 | |
10 | |
15 | |
20 | |
25 |
Task:
In cells A2 to A6, enter five numbers of your choice.
In cell B2, write a formula to add the value in cell A2 with the number 10.
Then, copy this formula from cell B2 to
cells B3 to B6. Observe the change in the formula as you copy it down.
Assignment 2: Multiply Each Value by a
Constant
Data:
C | D |
2 | |
4 | |
6 | |
8 | |
10 |
Task:
In cells C2 to C6, enter different numbers.
In column D, write a formula to multiply each value in column C by 5.
Copy the formula down through D6 using relative
references.
Assignment 3: Calculate Total Sales
Data:
E | F | G |
Quantity | Price/Unit | Total Sale |
3 | 50 | |
5 | 40 | |
2 | 30 | |
4 | 20 | |
6 | 10 |
Task:
Suppose you have the Quantity in column E (E2 to E6) and the Price per Unit in column F (F2 to F6).
Write a formula in column G to calculate the Total Sales for each item by multiplying the Quantity by the Price per Unit.
Use
relative references to copy the formula down the G column.
Assignment 4: Calculate Percentage of
Total
Data:
H | I |
Scores | Percentage of Total |
80 | |
90 | |
70 | |
85 | |
75 | |
Total: | 400 |
Task:
In cells H2 to H6, list the scores of students in a test .
In cell I2, write a formula to calculate the percentage of the total score that each score represents, where the Total Score is in cell H7.
Use relative
references to apply this calculation from I2 to I6.
Assignment 5: Find Running Total
Data:
J | K |
Values | Running Total |
10 | |
15 | |
20 | |
25 | |
30 |
Task:
In column J (cells J2 to J6), enter some numbers.
In column K, calculate the running
total for these values using relative references.
Assignment 6: Calculate Discounted Prices
Data:
A | B | C |
Product | Original Price | Discounted Price |
Shirt | 50 | |
Pants | 100 | |
Jacket | 150 | |
Shoes | 200 | |
Hat | 30 |
Task:
1. Apply a 20% discount on each product’s price in column B and display the discounted price in column C.
2. Use relative references to copy the formula down column C.
Assignment 7: Calculate Total Cost for Multiple Items
Data:
D | E | F G |
Item | Quantity | Price/Unit Total Cost |
Apples | 10 | 2 |
Bananas | 15 | 1.5 |
Oranges | 8 | 3 |
Grapes | 12 | 4 |
Mangoes | 5 | 5 |
Task:
1. In column G, calculate the total cost for each item by multiplying the Quantity in column E by the Price/Unit in column F.
2. Use relative references and copy the formula down from G2 through G6.
Assignment 8: Calculate Final Scores with Weights
Data:
H | I | J | K |
Student | Exam Score | Project Score | Final Score |
John | 80 | 90 | |
Alice | 85 | 70 | |
Bob | 78 | 88 | |
Lisa | 90 | 85 | |
Mike | 82 | 80 |
Task:
1. Calculate the Final Score by using a weighted formula where the Exam Score is 70% and the Project Score is 30%.
2. Use relative references to apply this formula from K2 to K6.
Assignment 9: Calculate Sales Commission
Data:
L | M | N | O |
Salesperson | Sales Amount | Commission Rate (%) | Commission Amount |
Sam | 5000 | 5 | |
Lucy | 8000 | 7 | |
Tom | 6000 | 6 | |
Jane | 4000 | 4 | |
Bill | 10000 | 8 |
Task:
1. Calculate the Commission Amount by applying the Commission Rate to the Sales Amount.
2. Use relative references to copy the formula from O2 to O6.
Assignment 10: Calculate Simple Interest
Data:
P | Q | R | S |
Principal | Rate (%) | Time (Years) | Simple Interest |
2000 | 5 | 2 | |
3000 | 4 | 3 | |
1500 | 6 | 5 | |
2500 | 3 | 4 | |
3500 | 7 | 1 |
Task:
1. Calculate the Simple Interest using the formula: Simple Interest=Principal×Rate100×Time\text{Simple Interest} = \text{Principal} \times \frac{\text{Rate}}{100} \times \text{Time}Simple Interest=Principal×100Rate×Time
2. Use relative references to copy the formula down from S2 to S6.
Assignment 11: Calculate Average Score
Data:
T | U | V | W | X |
Subject | Score 1 | Score 2 | Score 3 | Average Score |
Math | 85 | 90 | 88 | |
Science | 78 | 83 | 85 | |
English | 92 | 88 | 91 | |
History | 75 | 80 | 78 | |
Art | 88 | 85 | 90 |
Task:
1. Calculate the Average Score for each subject by averaging the values in columns U, V, and W.
2. Use relative references to apply the formula from X2 to X6.
Assignment 1: Basic Addition with Relative
References
Data:
|
A |
B |
|
5 |
|
|
10 |
|
|
15 |
|
|
20 |
|
|
25 |
Solution:
1. Enter values in cells A2 to A6 (e.g., 5, 10, 15, 20, 25).
2. In cell B2, enter the formula =A2 + 10.
3. Copy the formula in B2 and paste it down to B3 through B6.
4. Each formula in the B column will adjust automatically, becoming =A3 + 10 in B3, =A4 + 10 in B4, and so on.
Explanation:
When you copy the formula from B1 down to B5, Excel automatically adjusts the
formula to refer to each cell in column A. The values in B2 to B5 become =A2 +
10, =A3 + 10, etc., demonstrating how relative references adjust based on the
position.
Assignment 2: Multiplying by a Constant
Using Relative References
Data:
|
C |
D |
|
2 |
|
|
4 |
|
|
6 |
|
|
8 |
|
|
10 |
Solution:
1. Enter values in cells C2 to C6 (e.g., 2,4,6,8,10).
2. In cell D2, write the formula =C2 * 5.
3. Copy the formula in D2 and paste it down to D6.
4. The formula will adjust to =C3 * 5, =C4 * 5, and so on as it is pasted down.
Explanation:
As the formula is copied down, Excel adjusts the reference in column C. In cell
D2, the formula becomes =C2 * 3, in D3 it becomes =C3 * 3, and so on. This is a
typical use of relative references, allowing each row to reference the cell
directly to its left.
Assignment 3: Calculating Total Sales
Data:
|
E |
F |
G |
|
Quantity |
Price/Unit |
Total Sale |
|
3 |
50 |
|
|
5 |
40 |
|
|
2 |
30 |
|
|
4 |
20 |
|
|
6 |
10 |
Solution:
1. Enter quantities in cells E2 to E6 (e.g., 4, 5, 6, 7, 8).
2. Enter prices in cells F2 to F6 (e.g., 20, 25, 30, 35, 40).
3. In cell G2, enter the formula =E2 * F2 to calculate total sales for the first item.
4. Copy the formula in G2 down to G6. Each cell in G will automatically adjust to use the corresponding values from E and F, such as =E3 * F3 in G3.
Explanation:
As you copy the formula in column G, the reference changes to each row’s
respective quantity and price, like =E3 * F3, =E4 * F4, etc., using relative
references to calculate the Total Sale for each item.
Assignment 4: Calculate Percentage of
Total
Data:
|
H |
I |
|
Scores |
Percentage of Total |
|
80 |
|
|
90 |
|
|
70 |
|
|
85 |
|
|
75 |
|
|
Total: |
400 |
Solution:
1. Enter scores in cells H2 to H6.
2. In cell H7, enter the formula =SUM(H2:H6) to calculate the total score.
3. In cell I2, write the formula =H2 / H$7 * 100 to get the percentage of the total for the first score.
4. Copy the formula in I2 down to I6. The formula in each cell in column I will adjust to =H3 / H$7 * 100, =H4 / H$7 * 100, and so on, using the appropriate relative row in column H and the absolute reference to the total score in H7.
Explanation: The formula =H2 / $H$7 * 100 divides each score by the total score in H7. The $H$7 reference is absolute (fixed), while H2 is relative (changing). Copying this formula down lets Excel adjust only the relative part, resulting in each cell in I showing the respective percentage.
Assignment 5: Find Running Total
Data:
|
J |
K |
|
Values |
Running Total |
|
10 |
|
|
15 |
|
|
20 |
|
|
25 |
|
|
30 |
Solution:
1. Enter values in cells J2 to J6 (e.g., 10, 15, 20, 25, 30).
2. In cell K2, write the formula =J2 (the running total starts with the first value).
3. In cell K3, enter =K2 + J3 to add the next value to the running total.
4. Copy the formula in K3 down to K6. Each cell in column K will automatically reference the previous running total, updating it with the new value, such as =K3 + J4 in K4.
Explanation: The running total formula in K3 adds the previous total in K2 to the next value in J3. When you copy it down, each cell in K refers to the previous total, creating a cumulative sum that grows row by row, like =K3 + J4 in K4.
Assignment 6: Calculate Discounted Prices
Data:
|
A |
B |
C |
|
Product |
Original Price |
Discounted Price |
|
Shirt |
50 |
|
|
Pants |
100 |
|
|
Jacket |
150 |
|
|
Shoes |
200 |
|
|
Hat |
30 |
Task:
1.
Apply a 20% discount on each product’s
price in column B and display the discounted price in column C.
2.
Use relative references to copy the
formula down column C.
Solution:
1.
Enter the data as shown in columns A and
B.
2.
In cell C2, type =B2 * (1 - 0.20) and
press Enter to calculate a 20% discount on the original price.
3.
Copy the formula in C2 down to C6.
Explanation:
The formula =B2 * (1 - 0.20) calculates 80% of the original price in B2. When
copied down, the formula automatically adjusts to each row, such as =B3 * 0.8
for Pants, =B4 * 0.8 for Jacket, etc., calculating discounted prices with
relative references.
Assignment 7: Calculate Total Cost for
Multiple Items
Data:
|
D |
E |
F |
|
Item |
Quantity |
Price/Unit |
|
Apples |
10 |
2 |
|
Bananas |
15 |
1.5 |
|
Oranges |
8 |
3 |
|
Grapes |
12 |
4 |
|
Mangoes |
5 |
5 |
Task:
1.
In column G, calculate the total cost for
each item by multiplying the Quantity in column E by the Price/Unit
in column F.
2.
Use relative references and copy the
formula down from G2 through G6.
Solution:
1.
Enter data in columns D, E, and F.
2.
In cell G2, type =E2 * F2 to find the
total cost for Apples.
3.
Copy the formula from G2 down to G6.
Explanation:
When copied down, Excel adjusts the formula to refer to each row’s quantity and
price, such as =E3 * F3 for Bananas, =E4 * F4 for Oranges, etc., calculating
the total cost for each item dynamically.
Assignment 8: Calculate Final Scores with
Weights
Data:
|
H |
I |
J |
K |
|
Student |
Exam Score |
Project Score |
Final Score |
|
John |
80 |
90 |
|
|
Alice |
85 |
70 |
|
|
Bob |
78 |
88 |
|
|
Lisa |
90 |
85 |
|
|
Mike |
82 |
80 |
Task:
1.
Calculate the Final Score by using
a weighted formula where the Exam Score is 70% and the Project Score
is 30%.
2.
Use relative references to apply this
formula from K2 to K6.
Solution:
1.
Enter the data in columns H, I, and J.
2.
In cell K2, type =I2 * 0.7 + J2 * 0.3 and
press Enter.
3.
Copy the formula in K2 down to K6.
Explanation:
The formula =I2 * 0.7 + J2 * 0.3 calculates the final score by applying the
weights to the scores in columns I and J. When copied, Excel adjusts the cell
references, using =I3 * 0.7 + J3 * 0.3 for Alice, =I4 * 0.7 + J4 * 0.3 for Bob,
etc., to calculate each student’s weighted score.
Assignment 9: Calculate Sales Commission
Data:
|
L |
M |
N |
O |
|
Salesperson |
Sales Amount |
Commission Rate (%) |
Commission Amount |
|
Sam |
5000 |
5 |
|
|
Lucy |
8000 |
7 |
|
|
Tom |
6000 |
6 |
|
|
Jane |
4000 |
4 |
|
|
Bill |
10000 |
8 |
Task:
1.
Calculate the Commission Amount by
applying the Commission Rate to the Sales Amount.
2.
Use relative references to copy the
formula from O2 to O6.
Solution:
1.
Enter the data in columns L, M, and N.
2.
In cell O2, type =M2 * (N2 / 100) and press
Enter to calculate Sam's commission.
3.
Copy the formula from O2 down to O6.
Explanation:
The formula =M2 * (N2 / 100) calculates the commission for each salesperson by
multiplying the sales amount by the commission rate percentage. As you copy it
down, Excel adjusts the row numbers in each cell, like =M3 * (N3 / 100) for
Lucy, =M4 * (N4 / 100) for Tom, etc.
Assignment 10: Calculate Simple Interest
Data:
|
P |
Q |
R |
S |
|
Principal |
Rate (%) |
Time (Years) |
Simple Interest |
|
2000 |
5 |
2 |
|
|
3000 |
4 |
3 |
|
|
1500 |
6 |
5 |
|
|
2500 |
3 |
4 |
|
|
3500 |
7 |
1 |
Task:
1.
Calculate the Simple Interest using
the formula: Simple Interest=Principal×Rate100×Time\text{Simple Interest}
= \text{Principal} \times \frac{\text{Rate}}{100} \times
\text{Time}Simple Interest=Principal×100Rate×Time
2.
Use relative references to copy the
formula down from S2 to S6.
Solution:
1.
Enter the data in columns P, Q, and R.
2.
In cell S2, type =P2 * (Q2 / 100) * R2 and
press Enter.
3.
Copy the formula from S2 down to S6.
Explanation:
The formula =P2 * (Q2 / 100) * R2 calculates the simple interest based on the
principal, rate, and time values in each row. When copied down, Excel adjusts
the references for each row, so the interest for each principal is calculated
based on its respective rate and time.
Assignment 11: Calculate Average Score
Data:
|
T |
U |
V |
W |
X |
|
Subject |
Score 1 |
Score 2 |
Score 3 |
Average Score |
|
Math |
85 |
90 |
88 |
|
|
Science |
78 |
83 |
85 |
|
|
English |
92 |
88 |
91 |
|
|
History |
75 |
80 |
78 |
|
|
Art |
88 |
85 |
90 |
Task:
1.
Calculate the Average Score for
each subject by averaging the values in columns U, V, and W.
2.
Use relative references to apply the
formula from X2 to X6.
Solution:
1.
Enter the data as shown in columns T, U,
V, and W.
2.
In cell X2, type =AVERAGE(U2:W2) and press
Enter.
3.
Copy the formula from X2 down to X6.
Explanation:
The =AVERAGE(U2:W2) formula calculates the average of scores for each subject.
When copied, Excel adjusts the row references, so the formula uses
=AVERAGE(U3:W3) for Science, =AVERAGE(U4:W4) for English, etc., calculating
each subject’s average score.
Assignments using absolute reference In Excel
Assignment 1: Calculate Total Cost with Fixed Tax Rate
Data:
A | B | C | D |
Item | Price | Quantity | Total Cost |
Book | 15 | 2 | |
Pen | 3 | 10 | |
Notebook | 7 | 5 | |
Eraser | 1 | 20 | |
Pencil | 2 | 15 | |
Tax Rate | 10% |
Task:
1. Calculate the Total Cost by applying a fixed tax rate of 10% to the product of Price and Quantity for each item.
2. Use an absolute reference for the tax rate.
Assignment 2: Calculate Bonus with Fixed Percentage
Data:
F | G | H | I |
Employee | Basic Salary | Bonus % | Total Salary |
Alice | 5000 | ||
Bob | 6000 | ||
Carol | 5500 | ||
David | 7000 | ||
Emma | 8000 | ||
Bonus Rate | 5% |
Task:
1. Calculate each employee's Total Salary by adding a 5% bonus to their Basic Salary.
2. Use an absolute reference for the Bonus Rate.
Assignment 3: Calculate Profit with Fixed Overhead Cost
Data:
J | K | L | M |
Product | Revenue | Cost | Profit |
Table | 1000 | 700 | |
Chair | 500 | 200 | |
Sofa | 1500 | 1000 | |
Bed | 2000 | 1500 | |
Lamp | 300 | 100 | |
Overhead | 50 |
Task:
1. Calculate each product’s Profit by subtracting both the Cost and a fixed Overhead from the Revenue.
2. Use an absolute reference for the Overhead cost.
Assignment 4: Calculate Loan Payment with Fixed Interest Rate
Data:
O | P | Q |
Principal | Years | Monthly Payment |
10000 | 3 | |
15000 | 5 | |
20000 | 4 | |
25000 | 6 | |
30000 | 2 | |
Interest Rate | 5% |
Task:
1. Calculate the monthly payment for each loan using a fixed annual interest rate of 5% (use simple interest calculation for this example).
2. Use an absolute reference for the Interest Rate.
Assignment 5: Calculate Final Price with Fixed Markup Rate
Data:
S | T | U |
Product | Base Price | Final Price |
Pen | 20 | |
Pencil | 10 | |
Eraser | 5 | |
Ruler | 15 | |
Sharpener | 8 | |
Markup Rate | 30% |
Task:
1. Calculate each product's Final Price by applying a fixed Markup Rate of 30% to the Base Price.
2. Use an absolute reference for the Markup Rate.
Assignment 6: Calculate Travel Cost with Fixed Mileage Rate
Data:
W | X | Y | Z |
City | Distance (miles) | Fuel Efficiency (mpg) | Travel Cost |
City A | 100 | 20 | |
City B | 150 | 25 | |
City C | 200 | 30 | |
City D | 120 | 22 | |
City E | 180 | 28 | |
Fuel Price | 3.5 |
Task:
1. Calculate the Travel Cost by dividing Distance by Fuel Efficiency and multiplying by a fixed Fuel Price of $3.50.
2. Use an absolute reference for the Fuel Price.
Assignment 7: Calculate Final Grade with Fixed Weight for Exam
Data:
A | B | C | D | E |
Student | Assignment | Project | Exam | Final Grade |
John | 80 | 85 | 90 | |
Emma | 75 | 80 | 85 | |
Mike | 88 | 92 | 87 | |
Sara | 70 | 76 | 82 | |
Tom | 95 | 89 | 91 | |
Exam Weight | 50% |
Task:
1. Calculate the Final Grade by considering the Exam score with a weight of 50% and the Assignment and Project scores with a weight of 25% each.
2. Use an absolute reference for the Exam Weight.
Assignment 8: Calculate Commission for Sales with Fixed Rate
Data:
G | H | I | J |
Salesperson | Sales | Commission | Total Earnings |
Alice | 1500 | ||
Bob | 2000 | ||
Carol | 2500 | ||
David | 1800 | ||
Emma | 2200 | ||
Commission Rate | 10% |
Task:
1. Calculate each salesperson’s Commission using a fixed Commission Rate of 10%.
2. Add the commission to Sales to find the Total Earnings.
3. Use an absolute reference for the Commission Rate.
Assignment 9: Calculate Discounted Price with Fixed Discount Rate
Data:
L | M | N | O |
Product | Original Price | Discount | Final Price |
Shirt | 30 | ||
Pants | 50 | ||
Jacket | 100 | ||
Shoes | 75 | ||
Hat | 20 | ||
Discount Rate | 15% |
Task:
1. Calculate the Discount for each product using a fixed discount rate of 15%.
2. Subtract the Discount from the Original Price to get the Final Price.
3. Use an absolute reference for the Discount Rate.
Assignment 10: Calculate Monthly Savings with Fixed Interest Rate
Data:
Q | R | S | T |
Month | Savings | Interest Earned | Total Savings |
January | 500 | ||
February | 600 | ||
March | 700 | ||
April | 650 | ||
May | 800 | ||
Interest Rate | 2% |
Task:
1. Calculate the Interest Earned for each month by applying a 2% interest rate to the Savings.
2. Add the Interest Earned to the Savings to get the Total Savings.
3. Use an absolute reference for the Interest Rate.
Assignment 11: Calculate Total Cost of Fixed Utilities with Varying Usage
Data:
V | W | X | Y |
Utility | Usage (Units) | Unit Rate | Total Cost |
Electricity | 300 | ||
Water | 100 | ||
Gas | 150 | ||
Internet | 50 | ||
Garbage | 30 | ||
Fixed Rate per Unit | 5 |
Task:
1. Calculate the Total Cost for each utility by multiplying Usage (Units) by a fixed Unit Rate of $5.
2. Use an absolute reference for the Unit Rate.
Assignment 12: Calculate Salary with Fixed Deductions
Data:
AA | AB | AC | AD |
Employee | Basic Pay | Deduction | Net Salary |
Alice | 4000 | ||
Bob | 5000 | ||
Carol | 4500 | ||
David | 6000 | ||
Emma | 5500 | ||
Fixed Deduction | 200 |
Task:
1. Calculate each employee’s Net Salary by subtracting a fixed Deduction of $200 from the Basic Pay.
2. Use an absolute reference for the Deduction.
SOLUTION
Assignment 1: Calculate Total Cost with
Fixed Tax Rate
Data:
|
A |
B |
C |
D |
|
Item |
Price |
Quantity |
Total Cost |
|
Book |
15 |
2 |
|
|
Pen |
3 |
10 |
|
|
Notebook |
7 |
5 |
|
|
Eraser |
1 |
20 |
|
|
Pencil |
2 |
15 |
|
|
Tax Rate |
10% |
Task:
1.
Calculate the Total Cost by
applying a fixed tax rate of 10% to the product of Price and Quantity
for each item.
2.
Use an absolute reference for the tax
rate.
Solution:
1.
Enter the data as shown in columns A, B,
and C. In cell E1, type 0.10 (representing the 10% tax rate).
2.
In cell D2, enter the formula: =(B2 * C2)
* (1 + $E$1).
3.
Copy the formula from D2 down to D6.
Explanation:
The formula multiplies the Price by the Quantity and applies a
10% tax by referencing the fixed cell E1 (tax rate) using $E$1. The $ symbols
lock the reference, so when copied down, it remains fixed to the tax rate cell.
Assignment 2: Calculate Bonus with Fixed
Percentage
Data:
|
F |
G |
H |
I |
|
Employee |
Basic Salary |
Bonus % |
Total Salary |
|
Alice |
5000 |
||
|
Bob |
6000 |
||
|
Carol |
5500 |
||
|
David |
7000 |
||
|
Emma |
8000 |
||
|
Bonus Rate |
5% |
Task:
1.
Calculate each employee's Total Salary
by adding a 5% bonus to their Basic Salary.
2.
Use an absolute reference for the Bonus
Rate.
Solution:
1.
Enter the data as shown in columns F and
G. In cell I1, type 0.05 for the bonus rate.
2.
In cell H2, type the formula: =G2 * (1 +
$I$1) and press Enter.
3.
Copy the formula from H2 down to H6.
Explanation:
The formula uses $I$1 as an absolute reference to add the 5% bonus to each
employee’s Basic Salary. The $ symbols lock the bonus rate, keeping it
constant as the formula is copied to other cells.
Assignment 3: Calculate Profit with Fixed
Overhead Cost
Data:
|
J |
K |
L |
M |
|
Product |
Revenue |
Cost |
Profit |
|
Table |
1000 |
700 |
|
|
Chair |
500 |
200 |
|
|
Sofa |
1500 |
1000 |
|
|
Bed |
2000 |
1500 |
|
|
Lamp |
300 |
100 |
|
|
Overhead |
50 |
Task:
1.
Calculate each product’s Profit by
subtracting both the Cost and a fixed Overhead from the Revenue.
2.
Use an absolute reference for the Overhead
cost.
Solution:
1.
Enter the data as shown in columns J, K,
and L. In cell N1, type 50 for the overhead cost.
2.
In cell M2, type the formula: =K2 - L2 -
$N$1 and press Enter.
3.
Copy the formula from M2 down to M6.
Explanation:
The formula =K2 - L2 - $N$1 calculates the Profit by subtracting both
the Cost and Overhead from Revenue. The absolute reference
$N$1 ensures the overhead cost is fixed in each calculation.
Assignment 4: Calculate Loan Payment with
Fixed Interest Rate
Data:
|
O |
P |
Q |
|
Principal |
Years |
Monthly Payment |
|
10000 |
3 |
|
|
15000 |
5 |
|
|
20000 |
4 |
|
|
25000 |
6 |
|
|
30000 |
2 |
|
|
Interest Rate |
5% |
Task:
1.
Calculate the monthly payment for each
loan using a fixed annual interest rate of 5% (use simple interest calculation
for this example).
2.
Use an absolute reference for the Interest
Rate.
Solution:
1.
Enter the data as shown in columns O and
P. In cell R1, type 5%.
2.
In cell Q2, type the formula: =(O2 * (1 +
$R$1 * P2)) / (P2 * 12) and press Enter.
3.
Copy the formula from Q2 down to Q6.
Explanation:
This formula uses $R$1 as an absolute reference for the Interest Rate to
calculate each loan's monthly payment based on the Principal and Years
values. The $ symbols lock the interest rate, ensuring it remains constant as
the formula is copied.
Assignment 5: Calculate Final Price with
Fixed Markup Rate
Data:
|
S |
T |
U |
|
Product |
Base Price |
Final Price |
|
Pen |
20 |
|
|
Pencil |
10 |
|
|
Eraser |
5 |
|
|
Ruler |
15 |
|
|
Sharpener |
8 |
|
|
Markup Rate |
30% |
Task:
1.
Calculate each product's Final Price
by applying a fixed Markup Rate of 30% to the Base Price.
2.
Use an absolute reference for the Markup
Rate.
Solution:
1.
Enter the data as shown in columns S and
T. In cell V1, type 0.30.
2.
In cell U2, type the formula: =T2 * (1 +
$V$1) and press Enter.
3.
Copy the formula from U2 down to U6.
Explanation:
The formula =T2 * (1 + $V$1) calculates the Final Price by applying a
30% markup to the Base Price. The absolute reference $V$1 ensures the
markup rate stays the same for each row when the formula is copied down.
Assignment 6: Calculate Travel Cost with
Fixed Mileage Rate
Data:
|
W |
X |
Y |
Z |
|
City |
Distance (miles) |
Fuel Efficiency (mpg) |
Travel Cost |
|
City A |
100 |
20 |
|
|
City B |
150 |
25 |
|
|
City C |
200 |
30 |
|
|
City D |
120 |
22 |
|
|
City E |
180 |
28 |
|
|
Fuel Price |
3.5 |
Task:
1.
Calculate the Travel Cost by
dividing Distance by Fuel Efficiency and multiplying by a fixed Fuel
Price of $3.50.
2.
Use an absolute reference for the Fuel
Price.
Solution:
1.
Enter the data as shown in columns W, X,
and Y. In cell AA1, type 3.5.
2.
In cell Z2, type the formula: =(X2 / Y2) *
$AA$1 and press Enter.
3.
Copy the formula from Z2 down to Z6.
Explanation:
The formula =(X2 / Y2) * $AA$1 calculates the Travel Cost by dividing
the Distance by Fuel Efficiency and multiplying by the Fuel
Price. The absolute reference $AA$1 ensures that the fuel price stays the
same in each calculation as the formula is copied down.
Assignment 7: Calculate Final Grade with Fixed Weight for Exam
Data:
|
A |
B |
C |
D |
E |
|
Student |
Assignment |
Project |
Exam |
Final Grade |
|
John |
80 |
85 |
90 |
|
|
Emma |
75 |
80 |
85 |
|
|
Mike |
88 |
92 |
87 |
|
|
Sara |
70 |
76 |
82 |
|
|
Tom |
95 |
89 |
91 |
|
|
Exam Weight |
50% |
Task:
1.
Calculate the Final Grade by
considering the Exam score with a weight of 50% and the Assignment
and Project scores with a weight of 25% each.
2.
Use an absolute reference for the Exam
Weight.
Solution:
1.
Enter the data as shown in columns A
through D. In cell F1, type 0.5 for the exam weight.
2.
In cell E2, enter the formula: =B2 * 0.25
+ C2 * 0.25 + D2 * $F$1 and press Enter.
3.
Copy the formula from E2 down to E6.
Explanation:
The formula calculates each student’s Final Grade using a fixed Exam
Weight of 50%. The absolute reference $F$1 locks the weight, ensuring it
stays the same when copied to other cells.
Assignment 8: Calculate Commission for
Sales with Fixed Rate
Data:
|
G |
H |
I |
J |
|
Salesperson |
Sales |
Commission |
Total Earnings |
|
Alice |
1500 |
||
|
Bob |
2000 |
||
|
Carol |
2500 |
||
|
David |
1800 |
||
|
Emma |
2200 |
||
|
Commission Rate |
10% |
Task:
1.
Calculate each salesperson’s Commission
using a fixed Commission Rate of 10%.
2.
Add the commission to Sales to find
the Total Earnings.
3.
Use an absolute reference for the Commission
Rate.
Solution:
1.
Enter the data as shown in columns G and
H. In cell K1, type 0.1 for the commission rate.
2.
In cell I2, enter the formula: =H2 * $K$1
and press Enter.
3.
In cell J2, type the formula: =H2 + I2 to
calculate the Total Earnings.
4.
Copy the formulas down to I6 and J6.
Explanation:
The formula =H2 * $K$1 in column I calculates the Commission using an
absolute reference $K$1 for the commission rate. The formula in column J adds
the Sales and Commission to get the Total Earnings.
Assignment 9: Calculate Discounted Price
with Fixed Discount Rate
Data:
|
L |
M |
N |
O |
|
Product |
Original Price |
Discount |
Final Price |
|
Shirt |
30 |
||
|
Pants |
50 |
||
|
Jacket |
100 |
||
|
Shoes |
75 |
||
|
Hat |
20 |
||
|
Discount Rate |
15% |
Task:
1.
Calculate the Discount for each
product using a fixed discount rate of 15%.
2.
Subtract the Discount from the Original
Price to get the Final Price.
3.
Use an absolute reference for the Discount
Rate.
Solution:
1.
Enter the data as shown in columns L and
M. In cell P1, type 0.15 for the discount rate.
2.
In cell N2, type the formula: =M2 * $P$1
and press Enter.
3.
In cell O2, type the formula: =M2 - N2 and
press Enter.
4.
Copy both formulas down to N6 and O6.
Explanation:
The absolute reference $P$1 keeps the Discount Rate constant when
calculating the Discount for each product. The formula in column O
subtracts the Discount from the Original Price to give the Final
Price.
Assignment 10: Calculate Monthly Savings
with Fixed Interest Rate
Data:
|
Q |
R |
S |
T |
|
Month |
Savings |
Interest Earned |
Total Savings |
|
January |
500 |
||
|
February |
600 |
||
|
March |
700 |
||
|
April |
650 |
||
|
May |
800 |
||
|
Interest Rate |
2% |
Task:
1.
Calculate the Interest Earned for
each month by applying a 2% interest rate to the Savings.
2.
Add the Interest Earned to the Savings
to get the Total Savings.
3.
Use an absolute reference for the Interest
Rate.
Solution:
1.
Enter the data as shown in columns Q and
R. In cell U1, type 0.02 for the interest rate.
2.
In cell S2, type the formula: =R2 * $U$1
and press Enter.
3.
In cell T2, type the formula: =R2 + S2 to
calculate Total Savings.
4.
Copy the formulas down to S6 and T6.
Explanation:
The formula =R2 * $U$1 in column S calculates the Interest Earned using
an absolute reference to keep the Interest Rate fixed. The formula in
column T then adds Interest Earned to Savings for each month’s Total
Savings.
Assignment 11: Calculate Total Cost of
Fixed Utilities with Varying Usage
Data:
|
V |
W |
X |
Y |
|
Utility |
Usage (Units) |
Unit Rate |
Total Cost |
|
Electricity |
300 |
||
|
Water |
100 |
||
|
Gas |
150 |
||
|
Internet |
50 |
||
|
Garbage |
30 |
||
|
Fixed Rate per Unit |
5 |
Task:
1.
Calculate the Total Cost for each
utility by multiplying Usage (Units) by a fixed Unit Rate of $5.
2.
Use an absolute reference for the Unit
Rate.
Solution:
1.
Enter the data as shown in columns V and
W. In cell Z1, type 5 for the unit rate.
2.
In cell X2, type the formula: =W2 * $Z$1
and press Enter.
3.
Copy the formula down to X6.
Explanation:
The formula =W2 * $Z$1 multiplies Usage (Units) by a fixed Unit Rate
for each utility. The absolute reference $Z$1 ensures that the unit rate
remains the same when the formula is copied.
Assignment 12: Calculate Salary with Fixed
Deductions
Data:
|
AA |
AB |
AC |
AD |
|
Employee |
Basic Pay |
Deduction |
Net Salary |
|
Alice |
4000 |
||
|
Bob |
5000 |
||
|
Carol |
4500 |
||
|
David |
6000 |
||
|
Emma |
5500 |
||
|
Fixed Deduction |
200 |
Task:
1.
Calculate each employee’s Net Salary
by subtracting a fixed Deduction of $200 from the Basic Pay.
2.
Use an absolute reference for the Deduction.
Solution:
1.
Enter the data as shown in columns AA and
AB. In cell AE1, type 200 for the deduction.
2.
In cell AC2, type the formula: =AB2 -
$AE$1 and press Enter.
3.
Copy the formula down to AC6.
Explanation:
The formula =AB2 - $AE$1 calculates the Net Salary by subtracting a
fixed Deduction from Basic Pay. The absolute reference $AE$1
ensures the deduction value is fixed in each calculation.
Assignments from mixed references in MSExcel
Assignment 1: Calculate Product Cost with Variable Discounts per Category
Data:
A | B | C | D | E | F | G |
Product | Category | Price | Discount Rate | Total Cost | ||
Phone | Electronics | 500 | 10% | |||
TV | Electronics | 800 | 15% | |||
Shirt | Clothing | 50 | 20% | |||
Jeans | Clothing | 60 | 20% | |||
Toaster | Appliances | 30 | 5% | |||
Discount | Electronics | Clothing | Appliances | |||
Rate | 10% | 20% | 5% |
Task:
1. Calculate the Total Cost after applying a Discount for each Product.
2. Use a mixed reference for the discount rate so that it applies based on the Category in column B.
Assignment 2: Calculate Monthly Sales with Mixed Reference for Conversion Rate
Data:
J | K | L | M | N |
Product | Sales (USD) | Conversion Rate (USD to Local) | Sales (Local) | |
Laptop | 2000 | 1.3 | ||
Monitor | 1500 | 1.3 | ||
Mouse | 50 | 1.3 | ||
Keyboard | 70 | 1.3 | ||
Rate | USD to Local |
Task:
1. Convert Sales (USD) to Sales (Local) currency.
2. Use a mixed reference so the Conversion Rate in cell K2 remains constant while copying the formula.
Assignment 3: Calculate Marks as a Percentage of Total Using Mixed Reference
Data:
P | Q | R | S | T |
Student | Math | Science | English | Percentage |
Alex | 85 | 90 | 88 | |
Bella | 75 | 80 | 70 | |
Chris | 95 | 92 | 85 | |
Dana | 88 | 89 | 90 | |
Total Marks | 100 | 100 | 100 |
Task:
1. Calculate each student’s Percentage score, considering the Total Marks for each subject.
2. Use a mixed reference for the Total Marks row so that each subject’s total is consistent as the formula is copied.
Assignment 4: Calculate Bonus Based on Department with Fixed Department Bonus Rate
Data:
U | V | W | X | Y |
Employee | Department | Salary | Bonus Rate | Total Earnings |
John | Sales | 4000 | ||
Sara | IT | 5000 | ||
Mike | HR | 4500 | ||
Anna | IT | 5500 | ||
Steve | Sales | 4800 | ||
Bonus Rate | Sales | IT | HR | |
Rate | 5% | 8% | 4% |
Task:
1. Calculate each employee’s Bonus and add it to the Salary to get the Total Earnings.
2. Use a mixed reference for the Bonus Rate to apply each department's rate accordingly.
Assignment 5: Calculate Employee Sales Target Achievement as Percentage
Data:
Z | AA | AB | AC | AD |
Employee | Region | Sales | Target | Achievement (%) |
Adam | North | 3000 | 4000 | |
Bella | South | 2500 | 3500 | |
Carol | North | 4200 | 4000 | |
Dean | East | 2000 | 3000 | |
Elle | South | 3700 | 3500 | |
Region Targets | North | South | East | |
Base Target | 4000 | 3500 | 3000 |
Task:
1. Calculate each employee’s Achievement (%) by dividing their Sales by the Target.
2. Use a mixed reference so the base targets stay the same based on the Region.
Assignment 6: Employee Commission Based on Region and Sales
Data:
A | B | C | D | E | F |
Employee | Region | Sales | Commission (%) | Commission Earned | |
Emily | North | 5000 | |||
David | South | 7000 | |||
Mike | East | 6000 | |||
Laura | West | 4000 | |||
Region | North | South | East | West | |
Rate | 5% | 7% | 6% | 4% |
Task:
1. Calculate each employee’s Commission Earned based on their Region and Sales.
2. Use a mixed reference to keep the Commission Rate for each region fixed.
Assignment 7: Calculate Final Price after Tax Based on Product Category
Data:
G | H | I | J | K | L |
Product | Category | Price | Tax (%) | Final Price | |
Book | Education | 30 | |||
Headphones | Electronics | 150 | |||
T-shirt | Clothing | 25 | |||
Laptop | Electronics | 1000 | |||
Category | Education | Electronics | Clothing | ||
Tax Rate | 5% | 18% | 12% |
Task:
1. Calculate the Final Price for each product after adding the Tax.
2. Use mixed references to keep the Tax Rate based on Category.
Assignment 8: Calculate Total Score with Weighted Criteria
Data:
M | N | O | P | Q | R |
Student | Assignment | Quiz | Project | Total Score | |
Alice | 85 | 90 | 80 | ||
Bob | 75 | 85 | 78 | ||
Charlie | 95 | 92 | 88 | ||
Dana | 88 | 84 | 90 | ||
Weight | 0.3 | 0.2 | 0.5 |
Task:
1. Calculate each student’s Total Score based on weighted scores for Assignment, Quiz, and Project.
2. Use a mixed reference for Weights so each criterion remains consistent.
Assignment 9: Calculate Final Price with Seasonal Discount per Product Type
Data:
A | B | C | D | E | F |
Product | Type | Price | Discount % | Final Price | |
Shoes | Apparel | 80 | |||
Phone | Electronics | 500 | |||
Book | Education | 30 | |||
T-shirt | Apparel | 25 | |||
Discount | Apparel | Electronics | Education | ||
Rate | 15% | 10% | 5% |
Task:
1. Calculate each product's Final Price after applying the Discount based on Type.
2. Use a mixed reference to lock the Discount Rate while allowing the Type to vary.
Assignment 10: Calculating Cost with Varying Sales Tax for Different Items
Data:
I | J | K | L | M | N |
Item | Category | Price | Tax % | Final Cost | |
Shoes | Apparel | 50 | |||
Headphones | Electronics | 200 | |||
Books | Education | 15 | |||
TV | Electronics | 500 | |||
Category | Apparel | Electronics | Education | ||
Tax Rate | 7% | 15% | 3% |
Task:
1. Calculate the Final Cost of each item after applying the Sales Tax.
2. Use a mixed reference to keep Tax Rates fixed by Category.
SOLUTIONS
Assignment 1: Calculate Product Cost with
Variable Discounts per Category
Data:
|
A |
B |
C |
D |
E |
F |
G |
|
Product |
Category |
Price |
Discount Rate |
Total Cost |
||
|
Phone |
Electronics |
500 |
10% |
|||
|
TV |
Electronics |
800 |
15% |
|||
|
Shirt |
Clothing |
50 |
20% |
|||
|
Jeans |
Clothing |
60 |
20% |
|||
|
Toaster |
Appliances |
30 |
5% |
|||
|
Discount |
Electronics |
Clothing |
Appliances |
|||
|
Rate |
10% |
20% |
5% |
Task:
1.
Calculate the Total Cost after
applying a Discount for each Product.
2.
Use a mixed reference for the
discount rate so that it applies based on the Category in column B.
Solution:
1. Enter the data as shown in columns A through D. The discount rate for each category is in cells F2.
2.
In cell E2, enter the formula: =C2 * (1 -
INDEX($F$2:$F$4, MATCH(B2, $F$1:$H$1, 0))) and press Enter.
3.
Copy the formula from E2 down to E6.
Explanation:
This formula uses INDEX and MATCH functions to look up the Discount Rate
based on Category. The range $F$2:$F$4 uses absolute references to lock
the discount rate cells, while B2 is a relative reference that changes as you
copy the formula.
Assignment 2: Calculate Monthly Sales with
Mixed Reference for Conversion Rate
Data:
|
J |
K |
L |
M |
N |
|
Product |
Sales (USD) |
Conversion Rate (USD to Local) |
Sales (Local) |
|
|
Laptop |
2000 |
1.3 |
||
|
Monitor |
1500 |
1.3 |
||
|
Mouse |
50 |
1.3 |
||
|
Keyboard |
70 |
1.3 |
||
|
Rate |
USD to Local |
Task:
1.
Convert Sales (USD) to Sales
(Local) currency.
2.
Use a mixed reference so the Conversion
Rate in cell K2 remains constant while copying the formula.
Solution:
1.
Enter the data as shown in columns J
through L. In cell L1, type 1.3 for the conversion rate.
2.
In cell M2, type the formula =K2 * $L$1 to
calculate Sales (Local) and press Enter.
3.
Copy the formula down to M5.
Explanation:
The absolute reference $L$1 locks the Conversion Rate, while K2 is a
relative reference that changes based on the product's Sales (USD) value
as the formula is copied down.
Assignment 3: Calculate Marks as a
Percentage of Total Using Mixed Reference
Data:
|
P |
Q |
R |
S |
T |
|
Student |
Math |
Science |
English |
Percentage |
|
Alex |
85 |
90 |
88 |
|
|
Bella |
75 |
80 |
70 |
|
|
Chris |
95 |
92 |
85 |
|
|
Dana |
88 |
89 |
90 |
|
|
Total Marks |
100 |
100 |
100 |
Task:
1.
Calculate each student’s Percentage
score, considering the Total Marks for each subject.
2.
Use a mixed reference for the Total
Marks row so that each subject’s total is consistent as the formula is
copied.
Solution:
1.
Enter the data as shown in columns P
through S, with Total Marks in row 6.
2.
In cell T2, enter the formula =((Q2 /
$Q$6) + (R2 / $R$6) + (S2 / $S$6)) / 3 * 100 to calculate the Percentage.
3.
Copy the formula down to T5.
Explanation:
Here, $Q$6, $R$6, and $S$6 use absolute references to lock the Total Marks
row for each subject. The formula calculates each student's percentage by
dividing each subject's mark by the Total Marks and averaging them.
Assignment 4: Calculate Bonus Based on
Department with Fixed Department Bonus Rate
Data:
|
U |
V |
W |
X |
Y |
|
Employee |
Department |
Salary |
Bonus Rate |
Total Earnings |
|
John |
Sales |
4000 |
||
|
Sara |
IT |
5000 |
||
|
Mike |
HR |
4500 |
||
|
Anna |
IT |
5500 |
||
|
Steve |
Sales |
4800 |
||
|
Bonus Rate |
Sales |
IT |
HR |
|
|
Rate |
5% |
8% |
4% |
Task:
1.
Calculate each employee’s Bonus and
add it to the Salary to get the Total Earnings.
2.
Use a mixed reference for the Bonus
Rate to apply each department's rate accordingly.
Solution:
1.
Enter the data as shown in columns U
through V, with Bonus Rate in row 8.
2.
In cell X2, enter the formula =VLOOKUP(V2,
$U$8:$X$8, 2, FALSE) * W2 to calculate Bonus.
3.
In cell Y2, enter the formula =W2 + X2 for
Total Earnings.
4.
Copy both formulas down to X5 and Y5.
Explanation:
Using the Bonus Rate reference allows the formula to select the correct Bonus
Rate based on each department. The mixed reference allows $U$8:$X$8 to
remain constant while V2 varies for each row.
Assignment 5: Calculate Employee Sales
Target Achievement as Percentage
Data:
|
Z |
AA |
AB |
AC |
AD |
|
Employee |
Region |
Sales |
Target |
Achievement (%) |
|
Adam |
North |
3000 |
4000 |
|
|
Bella |
South |
2500 |
3500 |
|
|
Carol |
North |
4200 |
4000 |
|
|
Dean |
East |
2000 |
3000 |
|
|
Elle |
South |
3700 |
3500 |
|
|
Region Targets |
North |
South |
East |
|
|
Base Target |
4000 |
3500 |
3000 |
Task:
1.
Calculate each employee’s Achievement
(%) by dividing their Sales by the Target.
2.
Use a mixed reference so the base
targets stay the same based on the Region.
Solution:
1.
Enter the data as shown in columns Z
through AB, with Base Target values in row 9.
2.
In cell AD2, enter the formula =(AB2 /
VLOOKUP(AA2, $AA$9:$AC$9, 2, FALSE)) * 100 and press Enter.
3.
Copy the formula down to AD6.
Explanation:
This formula uses a mixed reference for the Target values in row
9, fixing them per Region with the VLOOKUP function. The VLOOKUP
reference $AA$9:$AC$9 remains constant, while AA2 adjusts per row to match the
correct region.
Assignment 6: Employee Commission Based on Region and Sales
Data:
|
A |
B |
C |
D |
E |
F |
|
Employee |
Region |
Sales |
Commission (%) |
Commission Earned |
|
|
Emily |
North |
5000 |
|||
|
David |
South |
7000 |
|||
|
Mike |
East |
6000 |
|||
|
Laura |
West |
4000 |
|||
|
Region |
North |
South |
East |
West |
|
|
Rate |
5% |
7% |
6% |
4% |
Task:
1.
Calculate each employee’s Commission
Earned based on their Region and Sales.
2.
Use a mixed reference to keep the Commission
Rate for each region fixed.
Solution:
1.
Enter the data as shown in columns A
through C, with region rates in row 6.
2.
In cell E2, type the formula =C2 *
VLOOKUP(B2, $B$6:$E$6, 2, FALSE) and press Enter.
3.
Copy the formula down to calculate the Commission
Earned for each employee.
Explanation:
Here, $B$6:$E$6 is an absolute reference, fixing the location of the Region
Rates while B2 changes for each employee. The Commission Rate is
determined based on each employee's region, and the formula calculates the
commission based on Sales.
Assignment 7: Calculate Final Price after
Tax Based on Product Category
Data:
|
G |
H |
I |
J |
K |
L |
|
Product |
Category |
Price |
Tax (%) |
Final Price |
|
|
Book |
Education |
30 |
|||
|
Headphones |
Electronics |
150 |
|||
|
T-shirt |
Clothing |
25 |
|||
|
Laptop |
Electronics |
1000 |
|||
|
Category |
Education |
Electronics |
Clothing |
||
|
Tax Rate |
5% |
18% |
12% |
Task:
1.
Calculate the Final Price for each
product after adding the Tax.
2.
Use mixed references to keep the Tax
Rate based on Category.
Solution:
1.
Enter the data as shown, with Category
and Tax Rate in columns K and L.
2.
In cell J2, enter =I2 * (1 + VLOOKUP(H2,
$K$2:$L$4, 2, FALSE)) and press Enter.
3.
Copy the formula down to fill in the Final
Price for each product.
Explanation:
This formula uses the mixed reference $K$2:$L$4 to locate the Tax Rate
for each Category. The VLOOKUP function allows the tax rate to vary by
category while keeping the range fixed, ensuring each Final Price
calculation is accurate.
Assignment 8: Calculate Total Score with
Weighted Criteria
Data:
|
M |
N |
O |
P |
Q |
R |
|
Student |
Assignment |
Quiz |
Project |
Total Score |
|
|
Alice |
85 |
90 |
80 |
||
|
Bob |
75 |
85 |
78 |
||
|
Charlie |
95 |
92 |
88 |
||
|
Dana |
88 |
84 |
90 |
||
|
Weight |
0.3 |
0.2 |
0.5 |
Task:
1.
Calculate each student’s Total Score
based on weighted scores for Assignment, Quiz, and Project.
2.
Use a mixed reference for Weights
so each criterion remains consistent.
Solution:
1.
Enter the data as shown, with weights in
row 7.
2.
In cell Q2, enter =(N2 * $N$7) + (O2 *
$O$7) + (P2 * $P$7) to calculate the Total Score and press Enter.
3.
Copy the formula down for each student.
Explanation:
The weights $N$7, $O$7, and $P$7 are absolute references, ensuring each
assignment type keeps the correct weight. As the formula is copied, the mixed
references allow each score to vary by student while keeping the weight
constant.
Assignment 9: Calculate Final Price with
Seasonal Discount per Product Type
Data:
|
A |
B |
C |
D |
E |
F |
|
Product |
Type |
Price |
Discount % |
Final Price |
|
|
Shoes |
Apparel |
80 |
|||
|
Phone |
Electronics |
500 |
|||
|
Book |
Education |
30 |
|||
|
T-shirt |
Apparel |
25 |
|||
|
Discount |
Apparel |
Electronics |
Education |
||
|
Rate |
15% |
10% |
5% |
Task:
1.
Calculate each product's Final Price
after applying the Discount based on Type.
2.
Use a mixed reference to lock the Discount
Rate while allowing the Type to vary.
Solution:
1.
Enter the data as shown in columns A
through F.
2.
In cell E2, enter =C2 * (1 - VLOOKUP(B2,
$F$2:$H$2, 2, FALSE)) to calculate the Final Price and press Enter.
3.
Copy the formula down for each product.
Explanation:
Using the mixed reference $F$2:$H$2 ensures that each Discount Rate is
based on the Type column while allowing the Price and Type
to vary by row. This provides flexibility for calculating the final discounted
price.
Assignment 10: Calculating Cost with
Varying Sales Tax for Different Items
Data:
|
I |
J |
K |
L |
M |
N |
|
Item |
Category |
Price |
Tax % |
Final Cost |
|
|
Shoes |
Apparel |
50 |
|||
|
Headphones |
Electronics |
200 |
|||
|
Books |
Education |
15 |
|||
|
TV |
Electronics |
500 |
|||
|
Category |
Apparel |
Electronics |
Education |
||
|
Tax Rate |
7% |
15% |
3% |
Task:
1.
Calculate the Final Cost of each
item after applying the Sales Tax.
2.
Use a mixed reference to keep Tax Rates
fixed by Category.
Solution:
1.
Enter the data as shown, with categories
and their respective Tax Rates in columns M and N.
2.
In cell L2, enter the formula =K2 * (1 +
VLOOKUP(J2, $M$2:$N$2, 2, FALSE)) to calculate the Final Cost.
3.
Copy the formula down to apply it to each
item.
Explanation:
The mixed reference $M$2:$N$2 ensures that the Tax Rate remains
consistent by category. As the formula is copied, it applies the correct tax
based on each Item’s Category.
