Maximum of Column A until 0

[csvirtual]

Hi!

In column B I need to have the maximum of all Rows in column A until a cell has the value 0.

Example:
..A B
1 2 3
2 3 3
3 1 1
4 0
5 1 5
6 3 5
7 5 5
8 1 1
9 0

Any help appreciated

thx
Christian

 

[RAM]

Hi,

This is not really as you presented it, but is this something like what is expected?

Book7
	A	B	C	D
1	1	1	1
2	2	3	3
3	3	1	3
4	4	1	4
5	5	0
6	6	3	6
7	7	5	7
8	8	1	8
9	9	0
10
11
Sheet1

Copy down the formula in C1.

Hope this helps

RAM

 

[Peter_SSs]

csvirtual

Try this. Formula in B1 (copied down):
=IF(A1=0,"",MAX(OFFSET(A1,0,0,MATCH(0,A1:A$9,0))))

Mr Excel.xls
	A	B	C	D
1	2	3
2	3	3
3	1	1
4	0
5	1	5
6	3	5
7	5	5
8	1	1
9	0
10
Max Till 0

 

[csvirtual]

@Ram: Thank You but the first Column was just the Row.

@Peter_SSs

Thank You. It's the right formula.
I have one Problem: I would like to have it this way and i just changed the Cells but it doesnt work (#NV):

=IF(W8=0,0,MAX(OFFSET(E8,0,0,MATCH(0,E8:e$65536,0))))

What's wrong with it?
If in Cell W... is a 0 than take the maximum of E65536 to E8 (as it should start from bottom) until W..=0

Hope You can help me again.

thanks

 

[Peter_SSs]

...
I have one Problem: I would like to have it this way and i just changed the Cells but it doesnt work (#NV):

=IF(W8=0,0,MAX(OFFSET(E8,0,0,MATCH(0,E8:e$65536,0))))

What's wrong with it?
If in Cell W... is a 0 than take the maximum of E65536 to E8 (as it should start from bottom) until W..=0

Hope You can help me again.

thanks

Please try to expalin again more clearly. Your formula starts off IF(W8=0,0,... but your explanation says 'If in Cell W... is a 0 than take the maximum of E65536 to E8'. These seem to contradict.

Perhaps you could give another small sample of data (columns E and W at least) and the expected results?

 

[csvirtual]

I hope you understand. I find it difficult to say it in words, as my english is not that good.

Thank you

 

[Peter_SSs]

OK

You were not far off. In AJ2 try this and copy down:
=IF(W2=0,0,MAX(OFFSET(E2,0,0,MATCH(0,W:W,0))))

Edit: Sorry this does not do it, I will need to re-think this.

 

[Peter_SSs]

Trying again, AJ2:
=IF(W2=0,0,MAX(OFFSET(E2,0,0,MATCH(0,W2:W$65536,0))))

This is close to what you had but the MATCH for zero needs to be on column W, not E. With this formula, the last value in column W will need to be 0 or there will be some #N/A errors at the bottom of column AJ.

 

[csvirtual]

Thank you peter!

It works fine.

have a nice in down under