# 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

M

#### Mandeep Baluja

Flanked by Zero means what ????

D

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

M

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

C

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

M

#### Mandeep Baluja

Heads off Clause Brilliant code

C

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

D

#### donotbeareporter

Thanks Claus.

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

D

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