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 th...
Excel Desktop
Formulas & Functions
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