Formula to find intermediate Max value in a column

  • Hi!


    I have a set of numbers in a column in a random order from cell A4 to cell A29.


    What I need is a formula to identify the lowest value in the range (already got it with =min(A4:A29) and the highest value from this lowest value to the bottom of the column.


    For example, in the below set of data, the lowest value is 2 and the highest value between 2 to the bottom of the column is 45 (even though the max of the range is 48, since it is above 2, it is being ignored).


    Data
    A
    B
    32
    11
    22
    33
    48 (this value will be ignored since it is above the min. of the range)
    14
    22
    2
    19
    37
    25
    27
    19
    29
    10
    30
    21
    24
    11
    17
    3
    29
    24
    45
    15
    10


    2 (Min)
    45 (Max)


    Note:

    1. Not looking for a macro since the data is dynamic and formula will be used for 250 columns. Would appreciate as simple a formula/s as possible (even if some helper cells are to be used). Also, I have found that array formulas tend to slow down calculations, so I would like to avoid those too).


    2. Fresh data is added at the top of the column and old data is gradually pushed down, so that there will be some blank cells below the last cell that contains data. Therefore the formula needs to address blank cells as well;


    3. There is some numeric data below cell A38. Therefore the formula needs to be restricted to range A4 to A29. Results will be held in Cells A34 (minimum) and Cell A35 (maximum).


    4. Sample file attached.


    Thanx,
    Naira

  • Re: Formula to find intermediate Max value in a column


    Assuming each number is unique in the list, try:


    [COLOR="#0000FF"]=MAX(INDEX(A4:A29,MATCH(A34,A4:A29,0)):A29)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!