1. Home

Excel 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

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 (1/F11)*B10
  • Hit enter

    • Note: The (1/F11) 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
    Range C2:D8 calculates the prices by multiplying item price and shop cart

  • Sub total
    The SUM function adds the range C2:C8

  • Add discount
    The discount is calculated by using F11(10) into the formula in B11

  • 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!

    function copyFormulas(elementId){ /* Get the text field */ var copyText = document.getElementById(elementId); /* Select the text field */ copyText.select(); copyText.setSelectionRange(0, 99999); /* For mobile devices */ /* Copy the text inside the text field */ navigator.clipboard.writeText(copyText.value) .then(() => { alert('Range copied to clipboard.\nIt can now be pasted into the Excel spreadsheet.') }) .catch((error) => { alert(`Copy failed! ${error}`) }) }

    Categories

    HTML CSS3 Bootstrap Foundation Sass JavaScript TypeScript JQuery AngularJS React NodeJS Highcharts PHP Python Python3 Django Linux Docker Ruby Java C C ++ Perl Servlet JSP Lua Scala Go ASP C # SQL MySQL SQLite MongoDB Redis Android Swift ionic Kotlin XML tutorial

    Contact US