Finding the largest value
 

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?

Finding the largest value
 

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?

Finding the largest value
 

Hi Kamal,

Can you clarify what you're attempting to search for? I guessed two
possible scenarios, given a 250Col x 50,000Row array:

1) Find the five consecutive columns whose sum of contents are largest
of all five-consecutive columns in the array.

2) Find the five consecutive columns whose largest row values summed
together are the largest of all sets of five-consecutive columns in the
array.

Scenario 1 can be done in Excel without VBA. Since you made no
requirement about specifying the specific columns with the maximum sum
value, you can add two totalling rows below the array:

Row 50001, for respective columns:
=Sum(A1:A50000) .... =Sum(IP1:IP50000)
Row 50002, Beginning with Column E:
=Sum(A50001:E50001) .... Sum(IL50001:IP50001)

...and then find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

...and finally divide that by five.

Scenario 2 can also be done in Excel without VBA. Again, you add two
new rows below the array:

Row 50001, for respective columns:
=max(A1:A50000) .... =max(IP1:IP50000)
Row 50002, beginning with column E:
= sum(A50001:E50001) .... =sum(IL50001:IP50001)

...and as earlier, find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

...and finally divide that by five.

===========
If I did not interpret your scenario properly, please reply with more
detail and I'm sure someone here will be able to help.

Glenn Ray
MOS Expert


---
Message posted from http://www.ExcelForum.com/

Finding the largest value
 

I suggest you send all values to a text-file or better a very easy small
access db, and use Excel (Sum(...) or SQL) to calculate the result in some
seconds....

David
www.big-numbers.com

"Glenn_Ray " schreef in bericht
...
Hi Kamal,

Can you clarify what you're attempting to search for? I guessed two
possible scenarios, given a 250Col x 50,000Row array:

1) Find the five consecutive columns whose sum of contents are largest
of all five-consecutive columns in the array.

2) Find the five consecutive columns whose largest row values summed
together are the largest of all sets of five-consecutive columns in the
array.

Scenario 1 can be done in Excel without VBA. Since you made no
requirement about specifying the specific columns with the maximum sum
value, you can add two totalling rows below the array:

Row 50001, for respective columns:
=Sum(A1:A50000) .... =Sum(IP1:IP50000)
Row 50002, Beginning with Column E:
=Sum(A50001:E50001) .... Sum(IL50001:IP50001)

..and then find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

..and finally divide that by five.

Scenario 2 can also be done in Excel without VBA. Again, you add two
new rows below the array:

Row 50001, for respective columns:
=max(A1:A50000) .... =max(IP1:IP50000)
Row 50002, beginning with column E:
= sum(A50001:E50001) .... =sum(IL50001:IP50001)

..and as earlier, find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

..and finally divide that by five.

===========
If I did not interpret your scenario properly, please reply with more
detail and I'm sure someone here will be able to help.

Glenn Ray
MOS Expert


---
Message posted from http://www.ExcelForum.com/

Finding the largest value
 

Dave,
I'm interested in seeing your SQL to solve this problem. Remember
there are 250 columns. Does 'consecutive columns' have any meaning in
SQL?

--

"ds" wrote in message .. .
I suggest you send all values to a text-file or better a very easy small
access db, and use Excel (Sum(...) or SQL) to calculate the result in some
seconds....

David
www.big-numbers.com

Finding the largest value
 

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?

Finding the largest value
 

Hi Glenn,

I hope I didn't explain my requirment correctly. The requirement is

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

It's not an issue for me to use Excel with VBA. Thanks for your help.

Kamal


Glenn_Ray wrote in message ...
Hi Kamal,

Can you clarify what you're attempting to search for? I guessed two
possible scenarios, given a 250Col x 50,000Row array:

1) Find the five consecutive columns whose sum of contents are largest
of all five-consecutive columns in the array.

2) Find the five consecutive columns whose largest row values summed
together are the largest of all sets of five-consecutive columns in the
array.

Scenario 1 can be done in Excel without VBA. Since you made no
requirement about specifying the specific columns with the maximum sum
value, you can add two totalling rows below the array:

Row 50001, for respective columns:
=Sum(A1:A50000) .... =Sum(IP1:IP50000)
Row 50002, Beginning with Column E:
=Sum(A50001:E50001) .... Sum(IL50001:IP50001)

..and then find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

..and finally divide that by five.

Scenario 2 can also be done in Excel without VBA. Again, you add two
new rows below the array:

Row 50001, for respective columns:
=max(A1:A50000) .... =max(IP1:IP50000)
Row 50002, beginning with column E:
= sum(A50001:E50001) .... =sum(IL50001:IP50001)

..and as earlier, find the maximum value of the sums in row 50002:
=max(E50002:IP50002)

..and finally divide that by five.

===========
If I did not interpret your scenario properly, please reply with more
detail and I'm sure someone here will be able to help.

Glenn Ray
MOS Expert


---
Message posted from http://www.ExcelForum.com/

Finding the largest value
 

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?

Finding the largest value
 

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?

Finding the largest value
 

Sue,

That's basically what I started with, too, but the recalc is a killer with
that many rows, so I switched to a macro solution to simplify.

Bernie
MS Excel MVP

"Sue Harsevoort" wrote in message
...
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?

Finding the largest value
 

Hi Bernie,

Thanks for helping hand. I tested with your macro for 12000 rows. It
took about 9 mins. For the whole 50000 rows, this iteration will take
more than 30 mins. Already we in our existing process are using about
2 hours to create these 50,000 rows. This new solution would add 30
more mins. If we find a solution to reduce the CPU time to 15 mins, I
can use that solution. Again thanks for your help.

Kamal

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

Finding the largest value
 

Hi Kamal,

Thanks for helping hand. I tested with your macro for 12000 rows. It
took about 9 mins. For the whole 50000 rows, this iteration will take
more than 30 mins. Already we in our existing process are using about
2 hours to create these 50,000 rows. This new solution would add 30
more mins. If we find a solution to reduce the CPU time to 15 mins, I
can use that solution. Again thanks for your help.

PMFJI, but you can do it in the worksheet instead of VBA:

250 columns is A:IP, so the following function in IQ1 would give the
figure you want:

=MAX(A1:IL1+B1:IM1+C1:IN1+D1:IO1+E1:IP1)/5

when array-entered (using shift+ctrl+enter)

So type that in IQ1 and copy it down the rest of the rows.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk