How to count the maximum number flanked by zeros

  • Thread starter Thread starter Tez
  • Start date Start date
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 ????
Click to expand...

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
Click to expand...

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:
Click to expand...

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
Click to expand...

Danke sehr! Sie sind super!!!!
 
Back
Top