How to find minimum sum of row values across multiple columns

[jaykrao999]

I have MS Excel 2007.

Hello. Hoping someone will be kind enough to answer my question

I have a spreadsheet containing 50 columns starting with col. B. Each column has 100 rows.

I need a function which will look at one column at a time, and for each column calculate the sum of the previous 20 rows ...where the 20th row is the current row, and then give me the lowest sum which was found among all 100 columns...this lowest sum would be put in col. A of each row. The formula would be put in col. A starting with row 20 (since the formula needs the previous 20 values).

I've attempted to do this using an array, but had no success. My best attempt was...
{=MIN(SUM(B1:AY20),B:AY)}. I copied this into A20:A100. Somewhat close but no cookie !!! I am able to find the lowest sum for the 20 rows at 81:100 but then this value gets put into all of the rows (20 - 79) which are above it. I'm very new to Excel so pardon me if this is a simple problem I would totally appreciate anyone's help in this.

[wfm007]

Can u plz attached the sample file

[jaykrao999]

Here is a sample worksheet attached which i abbreviated to 10 columns x 25 rows and instead of summing the last 20 rows in each column, say i want to sum the last 5.

I need a formula for col. A starting at a5 which will: 1) sum the last 5 rows for each column individually and then 2) find the minimum from step 1. So for example, for A25, the function should sum B21:B25, C21:C25,...K21:K25 (for a total of 10 sums, one for each column), then find the minimum value among these 10 sums and store that in A25. Similarly, the function should sum B1:B5, C1:C5,...K1:K5, find the minimum among these 10 sums, and store that in A5. Nothing would be stored in A1:A4 (since we need a minimum of 5 values). Thank you

[wfm007]

I think attached file is what u r looking for

[jaykrao999]

I originally thought of the same solution you are proposing...which is the most straightforward way....however, to be honest, I have multiple projects that this function needs to work on, and some of them have 900 columns...so this solution would be too cumbersome to spell out all 900 column references. I am looking for some sort of function where I can specify the beginning and ending columns. I was assuming this is done through an array...but I have no idea on how to create that to make it work in my problem. I need something which in effect does:

=MIN(SUM(A1:A5)...SUM(K1:K5)) (although of course this is not valid syntax !!!)