For full text search please use the '?' prefix. e.g. ? Onboarding

Data Sheet

This spreadsheet contains the collected price data point.

Note on spreadsheet

Sheet "data"

Cell A1

  • purpose: create the ordered column number
  • formula: 
    =ArrayFormula(SEQUENCE(1,MATCH(2,1/(A2:2<>""))))

Column "markup vs apple-us (vnd)"

  • purpose:
    • calculate markup price with respect to "apple-us": 1−PriceotherSellerPriceappleus1-\frac{Price_{otherSeller}}{Price_{appleus}}
  • formula: 
    =($G3-QUERY(
            {data!$B$3:$B,data!$C$3:$C,data!$D$3:$D,data!$E$3:$E,data!$F$3:$F,data!$G$3:$G},
            "SELECT Col6 WHERE Col1 contains '"&$B$3&"' AND Col2 contains '"&$C$3&"' AND Col3 contains '"&$D$3&"' AND Col4 contains '"&$E$3&"' AND Col5 contains '"&$F$3&"'"
           )
)/QUERY(
    {data!$B$3:$B,data!$C$3:$C,data!$D$3:$D,data!$E$3:$E,data!$F$3:$F,data!$G$3:$G},
    "SELECT Col6 WHERE Col1 contains '"&$B$3&"' AND Col2 contains '"&$C$3&"' AND Col3 contains '"&$D$3&"' AND Col4 contains '"&$E$3&"' AND Col5 contains '"&$F$3&"'"
    )

Column "helper_col_0"

  • purpose:
    • helper column to apply conditional formatting based on group of data
    • join the text from other columns, remove all non-printable characters, remove spaces
  • formula: 
    =ARRAYFORMULA(TRIM(CLEAN(SUBSTITUTE(B3:B&C3:C&D3:D&E3:E," ",""))))

Column "helper_col_1"

  • purpose:
    • helper column to apply conditional formatting based on group of data
    • if the text in Column "helper_col_0" are identical, the record belongs to the same group.
  • formula: 
    =MATCH($N3,UNIQUE($N$3:$N),0)

Conditional format rules

  • custom formula: 
    =ISEVEN($helper_col_1)
  • purpose:
    • apply color to group that has even numerical order in Column "helper_col_1"
    • using the $ sign to lock the column reference to column "helper_col_1"