Forum Discussion

RKD2313's avatar
RKD2313
Copper Contributor
Sep 22, 2022

Assistance With Trying To Find The Largest Discrepancy Between 2 Rows/1000+ Columns of Data

How can I find and sort the largest/smallest discrepancy between the values in 2 cells?

 

For Example: Lets say Row C and Row D are prices and I want to find which column(s) have the largest/smallest discrepancies of the values within the cells?

Formulas and Functions
  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor
    Sep 22, 2022

    Hi RKD2313 

     

    easiest solution: calculate the difference between column C and column D in a separate column (e.g. column E).

    =D1-C1

    And then sort the list in a descending order by column E

    • RKD2313's avatar
      RKD2313
      Copper Contributor
      Sep 22, 2022

      Let me be more specific. My fault that I wasnt in my initial post. The basis of this request is the fact that Im product hunting and looking for a more efficient way to source/scan data.

      I have 1000+ products (Columns)
      Each product has its price from Amazon & eBay (Rows C & D from my original example)

      In most instances the prices are very similar. Not enough of a margin to make a profit. However, there are products where its significantly cheaper on 1 marketplace vs the other. Instead of scanning Column by Column it would be nice if there was a formula or some type of lookup method so that the prices for each product is collected and compared between Amazon & eBay. Maybe discrepancy is the wrong wording? I basically just want to filter/sort the results so that I can see the biggest difference between prices for any 1 product. That would help save an enormous amount of time. I would hypothetically only have to look at around 50-150 Products vs scanning over each product individually 1000+ times over.

      • DexterG_III's avatar
        DexterG_III
        Iron Contributor
        Sep 24, 2022

        RKD2313 I think I have it.  See attached.   

         

        You just update the orange references (B1#,B2#,etc) below to the ranges in your workbook

        =LET(
Parts,B1#,
Amazon,B2#,
Other,B3#,
NumResults,B6,
AbVarPer,((Amazon-ABS(Amazon-Other))/Amazon),
AbVar,ABS(Amazon-Other),
HSTACK({"Part";"Amazon";"Other";"Abs Var";"Abs %"},SORT(FILTER(VSTACK(Parts,Amazon,Other,AbVar,AbVarPer),AbVarPer<=SMALL(AbVarPer,NumResults)),5,1,TRUE))
)

         And, as you can see, simply type how many of the top variances you want to review to get those results.  

        This uses Abs Variance as a Percentage of Amazon Price.  An Absolute Variance of $10 is a big deal for a $25 product when compared to a $10 variance on a $125 product.    

         

        Hope this solves your challenge.  Let me know of any questions.  

         

        Dexter

    • DexterG_III's avatar
      DexterG_III
      Iron Contributor
      Sep 22, 2022

      Martin_Weiss The only other consideration I could offer is using ABS(D1-C1) so that negative or positive bias is treated with the same weight for discrepancy.  100-2 and 2-100 are the same discrepancy but wouldn't be sorted as such with one being 98 and the other being -98.  Using ABS would force both to be 98 and sorted accordingly.  

      • RKD2313's avatar
        RKD2313
        Copper Contributor
        Sep 22, 2022
        Thanks for your reply as well. To give more specific insight I replied with more details.

Resources