How to count the maximum number flanked by zeros
 

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

How to count the maximum number flanked by zeros
 

Flanked by Zero means what ????

How to count the maximum number flanked by zeros
 

On Tuesday, November 18, 2014 5:13:59 PM UTC+8, Mandeep Baluja wrote:
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,

How to count the maximum number flanked by zeros
 

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.

How to count the maximum number flanked by zeros
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to count the maximum number flanked by zeros
 

Heads off Clause :) Brilliant code

How to count the maximum number flanked by zeros
 

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to count the maximum number flanked by zeros
 

Thanks Claus.

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

Cheers,

How to count the maximum number flanked by zeros
 

Hi,

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

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=9378A...121822A3%21326
for "MaxPerGroup"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to count the maximum number flanked by zeros
 

On Sunday, November 16, 2014 8:05:00 PM UTC+8, Tez wrote:
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!!!!