I have a range of cells with cash flow values some are positive and some are negative within different ranges.

I am trying to find the cell that contains the highest negative value and search for the next positive value after it.

Here is an example of the rage of cells:

I am trying to get the value of the $627,744

Here is what I have started thinking of: =min(a1:f1)

but I am not sure if it is possible to set a logic to extract the next positive value.

CodePudding user response：

You can try this formula

=INDEX(A1:F1,
    MATCH(1,--(INDEX(A1:F1,MATCH(MIN(A1:F1),A1:F1,0)):F1>0),0)
    MATCH(MIN(A1:F1),A1:F1,0)-1
 )

• This part

  INDEX(A1:F1,MATCH(MIN(A1:F1),A1:F1,0)):F1

  builds a variable range starting from the min-value (the INDEX-part) to the last value of the list (F1 in this case).

• This variable list is compared to 0.

• The --()-part converts the TRUE/FALSE-list to a 1/0-list.

• The surrounding MATCH finds the position of the first 1 (value greater zero).

• Then the position of the smallest value is added

  MATCH(MIN(A1:F1),A1:F1,0)

• Finally the overlapping range-part needs to be subtracted -1.

I hope this explanation is somewhat helpful. For further information i would refer to the formula evaluation-tool.