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

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,

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

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

Heads off Clause :) Brilliant code

Thanks Claus.

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

Cheers,

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

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