Forum Discussion

justwin029's avatar
justwin029
Copper Contributor
Jul 22, 2018

Excel Formula - Calculating the difference between the two identical values out of four and....

I have four data columns in an Excel worksheet, B:E. Sometimes there are two identical data values in a row. This is indicated by the 2 value in column F.

I'm trying to write a formula that, when there are identical data values in a row, calculates the difference between that value and the closest of the two remaining data values.

I'm also trying to write a formula to "label" the result of the previous formula.

 

For example, in row 20 we have B20=1107.45, C20=1107.3, D20=1107.45, & E20=1129.2.

Here, B20 and D20 have the same value, 1107.45 (thus F20=2). The closest of the remaining values is C20=1107.3, so the difference output should be 0.15 and the text label should be BEST, according to the following criteria:

  • Difference is 0.01 to 1.00 → BEST
  • Difference is 1.01 to 2.00 → GOOD

DATA FILEExpected ResultFormula UsedI want short but effective formula which cover above requirement for easy use.

Excel Desktop
Formulas & Functions

2 Replies

  • John Jairo Vergara Domínguez's avatar
    John Jairo Vergara Domínguez
    Brass Contributor
    Jul 23, 2018

    Hi, to both!

     

    You could try this shorter formulas:

    [Q2] : =IFERROR(AGGREGATE(15,6,1/(1/(MODE(B2:E2)-B2:E2))/(ABS(MODE(B2:E2)-B2:E2)<2)/(ABS(MODE(B2:E2)-B2:E2)<>1),1),"")

    [R2] : =IF(Q2="","",IF(ABS(Q2)<1,"BEST","GOOD"))

     

     

    Check file with formulas applied.  Blessings!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 23, 2018

    Hi,

     

    For results in numbers that could be as array formula

    =IFERROR(
   IF(
      ABS(
         AGGREGATE(14+(SUM(B2:E2-INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0)))<0), 6,
            1/(INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0))-B2:E2<>0)*
               (INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0))-B2:E2),1)
      ) <2,
         AGGREGATE(14+(SUM(B2:E2-INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0)))<0),6,
            1/(INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0))-B2:E2<>0)*
            (INDEX(B2:E2,MATCH(1,--(COUNTIF(B2:E2,B2:E2)>1),0))-B2:E2),1),
   ""),
"")

    and based on it result in text like

    =IF(LEN(Q2)=0,"",
   IF(Q2>1,"GOOD",
   IF(Q2>-1,"BEST",
   IF(Q2>-2,"GOOD",""
))))

    and attached

Resources