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/