Excel Case: Poke Mart Shop Cart
Case: Poke Mart Shop Cart
In this case we help the Poke Mart merchant to create a shop cart overview with prices, discount and total price. You will practice the skills that you have learned in the earlier chapters.
Copy values or type the following data:
To solve the case we need to complete the following:
- Prices for items (
C2:C8
) - Sub total (
B10
) - Add discount (
B11
) - Total price (
B13
)
Are you ready?
Start by creating the first formula for Item price*Shop
cart. We want the price to change when we change items in the Shop cart. Use the fill function on the range D2:D8
afterwards to save time.
Step by step:
- Select
C2
, type (=
) - Select
B2
- Type (
*
) - Select
D2
- Hit enter
Test the formula by Typing D2(1)
:
Did you get C2(200)
? That matches the price for one Pokeball!
Good job!
We need the same formula for C2:C8
, let's fill it downwards!
Note: We need relative references as we want the fill function to continue the formula to the next rows.
Fill D2:D8
to double check the prices:
That looks right!
Let us find the subtotal by using the SUM
function on the C2:C8
range.
- Type
B10(=SUM)
- Double click the
SUM
function - Mark the range
C2:C8
- Hit enter
Well done! You have successfully calculated the subtotal using the SUM
function.
Next, let's add a 10%
discount. Type F11(10)
:
Apply the discount to the subtotal:
- Select
B11
, type (=
) - Type
(F11/100)*B10
- Hit enter
Note: The (F11/100)
was used to change the value 10
to 0.1
, which is 10%
.
Subtract the discount B11
from the subtotal B10
to calculate the total.
- Select B13 type (
=
) - Select the minuend
B10
- Type (
-
) - Select the subtrahend
B11
- Hit enter
That is great!
You have completed all the tasks for the Poke Mart merchant.
-
Prices for items
RangeC2:D8
calculates the prices by multiplying item price and shop cart -
Sub total
TheSUM
function adds the rangeC2:C8
-
Add discount
The discount is calculated by usingF11(10)
into the formula inB11
-
Total price
Total price is calculated by subtracting the discount from the subtotal
Try changing the amounts in the shop cart and discount to see how the calculations adapt!
Case completed! Good job!