Search code examples
excel-formulamin

Excel MIN() if adjacent cell matches previous row's value

I am trying to design an Excel formula that, based on an adjacent column's value compared to the previous row's value returns the minimum of the column. Here is a specific example:

enter image description here

Given this data, the value 800 should be returned, because only 1800, 9000, and 800 should be considered, because those are the first cells where the Track column changes.

I have tried a variety of combinations with MIN() MINIFS(), MIN()/IF(), INDRIECT()/ADDRESS()/ROW()... but could not come up with a working example.

In pseudocode:

min_value = 999999
FOR EACH cell in A3 to A9

  IF value(adjacent cell in C) IS NOT value(adjacent cell in C - 1) THEN
    IF cell < min_value THEN
      set min_value to value(cell)
    END IF
  END IF

END FOR
Solution

  • The simple solution will based on Array Formulas.

    Enter the following formula: =MIN(IF(C2:C8<>C1:C7,A2:A8,"NULL"))

    Then press Ctrl + Shift + Enter.

    The formula will compare each cell in range C2:C8 to the cell above. When TRUE the min function will consider the corresponding cell in A2:A8 range otherwise NULL will be consider (that is some dummy string value that the min function will ignore)