Excel Advanced – Exercice 1

  • Convert to table – rename TBLOrders
  • Calculate Sales = @orderquantity x @unitprice
  • Calculate productstotals = @sales * (1-@discount)
  • AfterShipping = products total + shipping cost
  • use Floor and Ceiling formulas
  • Go to tax sheet –> change color sheet to red
  • Convert to table TBLTax
  • Vlookup (@province, TBLTax, 2, False)
  • TTC = after shippint * (1+tax)
  • Rename cell in variables sheet VCoursUSD
  • TTCLBP = @ttc * VCoursUSD
  • Profit = products total – sales (1 – product base marging)
  • Go to sheet customers
  • convert to table TBLCustomers
  • Data > Remove Duplicates
  • Create Colum Sales = Sumif(tblorders[customer name], A2, TBLorder[Ttc])
  • Create a Total Row
  • In TBLOrders create a total row > Compare Values
  • Go to Tax Sheet
  • Sum of Tax in new cell = sum(tblOrders[tax])
  • Count of orders in a new cell = count(tblOrders[order id])
  • Filter TBLorders for Pat Farhout
  • Copy columns “order date”, sales person, sales
  • Create a new sheet “Pat Farhout”
  • Paste values
  • Convert to table TBLPatFarhout
  • insert a pivot table in cell
    • Area: TBLPatFarhout
  • check Order Date
  • Check Sales
  • Drag sales > value fields settings (create count and average)

 

  • Use columns/rows wide functions
  • Use sheet wide functions
  • Choose function
  • Match / iferror
  • Index
  • Index – Match combination

Sample_Excel_Data

Leave a Reply

Your email address will not be published. Required fields are marked *