Here is a function version that adds a little flexablity:

Function MaxRange(curRange As Range, iNoCells As Integer) As Double

Dim i As Integer
Dim lMaxTotal As Long

For i = 0 To curRange.Columns.Count - iNoCells
lMaxTotal = Application.Max(lMaxTotal, Application.Sum( _
curRange.Range("A1").Offset(0, i).Resize(1, iNoCells)))
Next

MaxRange = lMaxTotal

End Function

This does seem to be slightly slower (though faster than it was before I
incorparted some of Bernie's code), but it give the flexablity of selecting
the range size if that might be needed at some time. To use it you would
put this formula in cell A251 for example: =MaxRange(A1:A250,5)/5 and then
just fill it down to the other cells.

Sue


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Kamal,

Copy the macro below, and paste it into a code module in your workbook.
Then run the macro PutMaxAvgValues.

HTH,
Bernie
MS Excel MVP

Option Explicit
Sub PutMaxAvgValues()
Dim myRows As Long
Dim myCol As Integer
Dim myMax As Double

For myRows = 1 To ActiveSheet.UsedRange.Rows.Count
myMax = Application.Min(Range(myRows & ":" & myRows))
For myCol = 1 To 246
myMax = Application.Max(myMax, Application.Sum( _
Cells(myRows, myCol).Resize(1, 5)))
Next myCol
Cells(myRows, 251).Value = myMax / 5
Application.StatusBar = "Now doing row " & myRows & _
" of " & ActiveSheet.UsedRange.Rows.Count
Next myRows
Application.StatusBar = False
End Sub





"Kamal" wrote in message
om...
Bernie,

I prefer writing VBA macros for this scenario. However, I don't know
how to write a VBA macro. If you could help me that would be very
greatful.

The requirment is.

Col 251 for every row = [Max(Sum(A1:E1),Sum(B1:F1),....Sum(246th
col:250th col))]/5

Thanks in advance for your help.

Kamal

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Kamal,

No. There is no better way to do that. You're stuck iterating
through
all
the possibilities.

I suggest using a VBA macro or function rather than formulas. If you
need
help writing it, let us know.

HTH,
Bernie
MS Excel MVP

"Kamal" wrote in message
om...
I have an excel sheet with 250 columns and 50,000 rows. My
requirment
is to get the largest sum total of any 5 CONSECUTIVE columns and
divide that sum by 5. To get the largest value for any row I have to
do 246 iterations. To find the required value for all the rows it
would take lot of processing time. Is there any better way of doing
this?