Finding Max of Column that Varies

[QBtrades]

I want to find the Max of a column if other columns have 1's in it but the 1's vary.

Here is an example: I want to find the Max of column A after the first 1 in column B shows until the next 1 in column C shows above.

So in this example the first 1 in Column B shows at A7. So the Max from A6:A1 (next 1 above in Column C is at C1) is 15. There is a 1 in A10 with the first 1 in column C above it at C9. So the Max of A9 issue 10...and so forth

A---------B---------C---------D
10--------0---------1---------15
12--------0---------0---------""
14--------0---------0---------""
12--------1---------0---------""
10--------1---------0---------""
15--------1---------0---------""
20--------1---------0---------""
25--------0---------0---------""
10--------0---------1---------10
12--------1---------0---------""
14--------0---------0---------""
12--------0---------0---------""
10--------0---------1---------15
15--------1---------0---------""
20--------1---------0---------""
25--------0---------0---------""

The "" are just blank cells

Hope this makes sense.

 

[Aladin Akyurek]

I worded it wrong. B7 is the 1st 1 from the bottom after a 0 in the sequence from B7 to B4

So B10 is the 1st 1 in that sequence

And B15 is the 1st B in that sequence from B15 to B14

Does this makes sense?

No, not at all.

We have a 1 series in B from B4:B7. How do you conclude to 15 from here?

 

[QBtrades]

So B7, B6, B5, and B4 all have 1's. B7 is the 1st 1 in that sequence(B4:B7) from the bottom of the sequence(B7). So I get 15 because it is the MAX (A4:A6). The formula needs to go by the next 1 up in Column C after the Column B sequence ends. So in my example the 15 is the MAX of A4:A6 and placed in D1 because C1 has a 1 in it. It is the first 1 up after the B4:B7 sequence of 1's end.

 

[Aladin Akyurek]

In D1 control+shift+enter, not just enter, and copy down:

=IFERROR(IF(C1=1,MAX(OFFSET(A1,0,0,MAX(IF(IF(B2:$B$16=1,ROW(B2:$B$16)-ROW(B2)+1)<LOOKUP(9.99E+307,CHOOSE({1,2},ROWS(C2:$C$16),MATCH(1,C2:$C$16,0))),IF(B2:$B$16=1,ROW(B2:$B$16)-ROW(B2)+1))))),""),"")

Would you test if this behaves reliably?

 

[QBtrades]

It doesn't not work. It's missing something because it just gives a warning when trying to enter it

 

[Aladin Akyurek]

It doesn't not work. It's missing something because it just gives a warning when trying to enter it

Again that < sign!...

In D1 control+shift+enter and copy down:

=IFERROR(IF(C1=1,MAX(OFFSET(A1,0,0,MAX(IF(IF(B2:$B$16=1,ROW(B2:$B$16)-ROW(B2)+1) < LOOKUP(9.99E+307,CHOOSE({1,2},ROWS(C2:$C$16),MATCH(1,C2:$C$16,0))),IF(B2:$B$16=1,ROW(B2:$B$16)-ROW(B2)+1))))),""),"")

 

[QBtrades]

Formula doesn't give me an error anymore but it doesn't work. Thanks

 

[Aladin Akyurek]

Formula doesn't give me an error anymore but it doesn't work. Thanks

Control+shift+enter >> Press down the control and the shift keys at the same time while you hit the enter key. If done coorectly, Excel itself puts a pair of { and } around the formula in recognition.

 

[QBtrades]

Yes I know how to do that. It still didn't show anything. Column D is blank after I copy it down after entering the array.

 

[Aladin Akyurek]

Yes I know how to do that. It still didn't show anything. Column D is blank after I copy it down after entering the array.

Select D1. Look at the formula bar. Do you see { and } around the formula?

 

[QBtrades]

Yes I do. I know how to enter an array. I'm not that much of a rookie to excel. I did everything you said. Formula doesn't work for me. Thanks