Finding Max of Column that Varies

Q

QBtrades

New Member
Joined
Apr 18, 2017
Messages
17
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
It seems column D of your exhibit shows the desired output. If so, how does the first value, i.e. 15, obtains?
 
Upvote 0
It's copied from some reports. Formula in column D is what I need. I put those numbers manually to show for example
 
Upvote 0
I entered it manually because it was the max number I'm looking for in Column A.

I want to have a formula in Column D instead of entering it manually.

The reason it is 15 is because the 1st 1 starts in B7. The formula needs to need when we get to the next 1 in column C. This happens in C1 in my example. So the formula needs to find the Max of A1:A6 which is 15
 
Upvote 0
QBtrades said:
I entered it manually because it was the max number I'm looking for in Column A.

I want to have a formula in Column D instead of entering it manually.

The reason it is 15 is because the 1st 1 starts in B7. The formula needs to need when we get to the next 1 in column C. This happens in C1 in my example. So the formula needs to find the Max of A1:A6 which is 15
Click to expand...

Row\Col
A​
B​
C​
D​
1​
10​
0​
1​
15​
2​
12​
0​
0​
3​
14​
0​
0​
4​
12​
1​
0​
5​
10​
1​
0​
6​
15​
1​
0​
7​
20​
1​
0​
8​
25​
0​
0​
9​
10​
0​
1​
10​
10​
12​
1​
0​
11​
14​
0​
0​
12​
12​
0​
0​
13​
10​
0​
1​
15​
14​
15​
1​
0​
15​
20​
1​
0​
16​
25​
0​
0​

As you can see, the 1st 1 in column B does not start in B7!...
 
Upvote 0
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Find Max of Year
2
Replies
12
Views
182
bobsan42
bobsan42
T
  • Question
Combining long formulas in Excel
2
Replies
18
Views
429
tzav
T
S
  • Question
Formula for Equity Vesting
Replies
6
Views
331
Dave Patton
D
S
  • Solved
Show the largest date and the smallest date on the listbox Userform
Replies
5
Views
133
Domenic
D
F
  • Question
REDUCE function not working when feeding result of FILTER function. Why?
Replies
9
Views
305
Fluff
Fluff

Forum statistics

Threads
1,214,845
Messages
6,121,902
Members
449,053
Latest member
Guy Boot

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top