Find the Min Value since the last Max value

[FatherTed]

Hi,

I have a column of values and as I go down the column at each new high value I would like to calculate the min value since the last high value.
I know the following formula is not valid but it's what I would like to do: =if(BV121=max(BV2:BV121),min(max(BV2:BV120):bv120),"")
Is it possible?

Very many thanks in advance,

Ted

[zbor]

Try this in BV2 and pull down:

=IF(BV2=max($BV$2:$BV$121),min(BV2:$BV$121),"")

[FatherTed]

Thanks - that finds the MIN in all the values up to the point where the MAX for the column is.

But what I want to do is different. I want to start at the top of the column and go down through each cell seeing if that cell is the MAX up to that point in the column. If it is, then I want to find out what the MIN value is since the previous MAX was found in that column.

Is that possible?

[NBVC]

Do you mean?

=IF($BV$121=MAX($BV$2:$BV$121),"",MIN(INDEX($BV$2:$BV$121,MATCH(MAX($BV$2:$BV$121),$BV$2:$BV$121,0)):$BV$121))

edit:

Add error trap for if last value is the Max

[canapone]

In BW121 you could try

Please Login or Register  to view this content.

or in BW3 and down

Please Login or Register  to view this content.

Regards

[Fotis1991]

Hi

Pls take a look to the sample, using a helper column.

Hope to helps you.

[FatherTed]

Thanks again - that returned the MAX value of that column.

The column is 150 cells long. Say I start with 0 in cell 1, then i have negative numbers until in cell 10 I have a value of 15. So I want to calculate the MIN between cell 1 and 10. The values of the cells are all below 15 until cell 54 when I have a value of 30, so I now want to calculate the value of the MIN between the 15 in cell 10 and the new MAX of 30 in cell 54. And so on down the column as each new higher MAX is reached.

I really appreciate your answers.

Any more ideas?

[zbor]

Upload example workbook and write your input and desired output

[FatherTed]

Thank you so much everyone - Canapone's answer works for me!!!

All your help is very much appreciated :-)

Ted