Conditional Max

[Mika]

Hi,

I have in column A, several ranges separated for, lets´s say the
character "-". I need to find the max of that range and put it in
column b, however I can not find a formula that find the maximum of a
sequence of numbers until it finds the first "-"
Of course, the numbers in columa A are the result of formulas, so the
order values etc changes with user input.

The solution should look like:

column A Column B
-
-
2 6
5
1
6
-
3 8
8
1
-
-
-
Thanks for your time
Mika

 

[Don Guillett]

I just did one like this for a client. You need a macro to find the first -
then find the second - and max >then loop to the next group until finished.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hi,

I have in column A, several ranges separated for, lets´s say the
character "-". I need to find the max of that range and put it in
column b, however I can not find a formula that find the maximum of a
sequence of numbers until it finds the first "-"
Of course, the numbers in columa A are the result of formulas, so the
order values etc changes with user input.

The solution should look like:

column A Column B
-
-
2 6
5
1
6
-
3 8
8
1
-
-
-
Thanks for your time
Mika

 

[Guest]

Try something like this:

With your list of values in Col_A, with a column title in A1 and values, or
dashes, below"

B2:
=IF(AND(A1="-",ISNUMBER(A2)),MAX(OFFSET(A2,,,MATCH("-",$A2:$A$65536,0),1)),"")

Copy B2 down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Mika]

Thanks both,


Don you are right with vba is piece of cake, but not possible for this
work.

Ron, I included at the beggining of those cells a comment:
"solution proposed by Ron Coderre"


Thanks it worked like a charm !

Mika

 

[Guest]

Thanks for the feedback, Mika....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP

 

[Don Guillett]

not possible for this work.
Why not

lr = Cells(Rows.Count, "a").End(xlUp).Row
c1 = 1
Do Until c1 + 1 >= lr
c1 = Range(Cells(c1, 1), Cells(lr, 1)).Find("-").Row
c2 = Range(Cells(c1 + 1, 1), Cells(lr, 1)).Find("-").Row - 1
Cells(c2, 2) = Application.Max(Range(Cells(c1, 1), Cells(c2, 1)))
c1 = Range(Cells(c2, 1), Cells(lr, 1)).Find("-").Row - 1
Loop

 

[Don Guillett]

Improvement if no - at the bottom

lr = Cells(Rows.Count, "a").End(xlUp).Row
If Cells(lr, 1) <> "-" Then
Cells(lr + 1, 1) = "-"
lr = lr + 1
End If
c1 = 1
Do Until c1 + 1 >= lr
c1 = Range(Cells(c1, 1), Cells(lr, 1)).Find("-").Row
c2 = Range(Cells(c1 + 1, 1), Cells(lr, 1)).Find("-").Row - 1
Cells(c2, 2) = Application.Max(Range(Cells(c1, 1), Cells(c2, 1)))
c1 = Range(Cells(c2, 1), Cells(lr, 1)).Find("-").Row - 1
Loop

 

[Mika]

Thanks Don,

Sorry for not explaining clearly, but it is not allowed to use macros
in this spreadsheet. That´s why I didn´t post it in the program area.

Rgd
Mika