# How to count the maximum number flanked by zeros

T

#### 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

Flanked by Zero means what ????

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,

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.

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.

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.

Thanks Claus.

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

Cheers,

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!!!!