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