How to count the maximum number flanked by zeros

[Tez]

Hi all!

I have some numbers zeros and non-zeros in Column A and would like to see the maximum number flanked by zeros in Column A's non-zero range in Column B.

What formula should be written in cells in Column B?

A B

0 0
0 0
3 7
2 7
6 7
7 7
0 0
0 0
0 0
8 8
2 8
3 8
0 0

 

[Mandeep Baluja]

Flanked by Zero means what ????

 

[donotbeareporter]

Flanked by Zero means what ????

Basically a range sandwiched by zeros.

So, a first instance of that in the above example is a series of numbers of 3, 2, 6 and 7 and I want to print 7, the maximum number of these four numbers, next to each one of them in the next column.

Thanks,

 

[Mandeep Baluja]

way to Go !! It tooks three hours to get this result with the formula.

=IF(IF(A3<>0,MAX(OFFSET(A2,,,MATCH(0,A3:A15,0),1),B1))=FALSE,"",IF(A3<>0,MAX(OFFSET(A2,,,MATCH(0,A3:A15,0),1),B1)))

Thanks
Mandeep Baluja
Excel Specialist.

Send me your email id I can send you the file tooo.

 

[Claus Busch]

Hi Tez,

Am Sun, 16 Nov 2014 04:04:56 -0800 (PST) schrieb Tez:

A B

0 0
0 0
3 7
2 7
6 7
7 7
0 0
0 0
0 0
8 8
2 8
3 8
0 0

try it with VBA:

Sub MaxPerGroup()
Dim i As Long, LRow As Long
Dim StartRng As Long, EndRng As Long

With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LRow
If .Cells(i, 1) = 0 And .Cells(i + 1, 1) > 0 Then
StartRng = i + 1
ElseIf .Cells(i, 1) > 0 And .Cells(i + 1, 1) = 0 Then
EndRng = i
.Range(.Cells(StartRng, 2), .Cells(EndRng, 2)) = _
WorksheetFunction.Max(Range(.Cells(StartRng, 1), _
.Cells(EndRng, 1)))
End If
Next
End With
End Sub


Regards
Claus B.

 

[Mandeep Baluja]

Heads off Clause Brilliant code

 

[Claus Busch]

Hi again,

Am Wed, 19 Nov 2014 11:21:48 +0100 schrieb Claus Busch:

try it with VBA:

that it looks like your column B:

Sub MaxPerGroup()
Dim i As Long, LRow As Long
Dim StartRng As Long, EndRng As Long

Application.ScreenUpdating = False
With ActiveSheet
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LRow
If .Cells(i, 1) = 0 And .Cells(i + 1, 1) > 0 Then
StartRng = i + 1
ElseIf .Cells(i, 1) > 0 And .Cells(i + 1, 1) = 0 Then
EndRng = i
.Range(.Cells(StartRng, 2), .Cells(EndRng, 2)) = _
WorksheetFunction.Max(Range(.Cells(StartRng, 1), _
.Cells(EndRng, 1)))
End If
Next
.Range("B1:B" & LRow).SpecialCells(xlCellTypeBlanks) = 0
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[donotbeareporter]

Thanks Claus.

I wonder still if there is an elegant worksheet function to insert, as an array formula?

Cheers,

 

[Claus Busch]

Hi,

Am Wed, 19 Nov 2014 20:32:22 -0800 (PST) schrieb
(e-mail address removed):

I wonder still if there is an elegant worksheet function to insert, as an array formula?

with a helper column you can do it with an array formula.
Have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "MaxPerGroup"


Regards
Claus B.

 

[donotbeareporter]

Hi all!

I have some numbers zeros and non-zeros in Column A and would like to see the maximum number flanked by zeros in Column A's non-zero range in Column B.

What formula should be written in cells in Column B?

A B

0 0
0 0
3 7
2 7
6 7
7 7
0 0
0 0
0 0
8 8
2 8
3 8
0 0

Danke sehr! Sie sind super!!!!