- 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