CODEWITHSUNDEEP
excelexcel-formula
Paul Pieless
Paul Pieless

Reputation: 59

Excel: Make a dynamic formula that counts a specified max sum of X consecutive days

I am trying to make a formula that could count the max sum of any number of consecutive days that I indicate in some cell. Here is the dataset and the formula: Dataset

The formula that calculates the maximum sum of three consecutive days:

=MAX(IFERROR(INDEX(
INDEX(E2:AI2,0)+
INDEX(F2:AI2,0)+
INDEX(G2:AI2,0),
0),""))

As you can see the number of days here is determined by the number of rows in the formula that start with "Index". The only difference between these rows is the letters (E, F, G). Is there any way I could reference a cell in which I could put a number for those days, instead of adding more rows to this formula?

Upvotes: 1

Views: 475

Answers (3)

P.b
P.b

Reputation: 11588

Another approach getting to the same result:

=LET(range,E2:AI2,
     cons,4,
     repeat,COLUMNS(range)-cons+1,
MAX(
    BYROW(SEQUENCE(repeat,cons,,1)-INT(SEQUENCE(repeat,cons,0,1/cons))*(cons-1), 
    LAMBDA(x,SUM(INDEX(range,1,x))))))

This avoids OFFSET (volatile, slowing your file down) and the repeat value, consecutive number and/or the range are easily changeable.

Hope it helps (I answered to the max sum, as stated in the title). Change max to min to get the min sum result.

Edit: I changed the repeat part in the formula to be dynamic (max number of consecutive columns in range), but you can replace it by a number or a cell reference. The cons part can also be linked to a cell reference.

Also found a big in my formula which is fixed.

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34265

Another approach avoding use of Offset is to use Scan to generate an array of running totals, then subtract totals which are N elements apart (where N is the number of consecutive cells to be added):

=LET(range,E2:AI2,
length,A1,
runningTotal,SCAN(0,range,LAMBDA(a,b,a+b)),
sequence1,SEQUENCE(1,COLUMNS(range)-length+1,A1),
sequence2,SEQUENCE(1,COLUMNS(range)-length+1,0),
difference,INDEX(runningTotal,sequence1)-IF(sequence2,INDEX(runningTotal,sequence2),0),
MAX(difference))

enter image description here

Upvotes: 1

Paul Pieless
Paul Pieless

Reputation: 59

The answer here was posted by another user on another website, so I will repost it here:

One way to achieve this without relying on a VBA solution would be to use the BYCOL() function (available for Excel for Microsoft 365):

=BYCOL(array, [function]) The array specifies the range to which you want to apply your function, and the function itself is specified in a lambda statement. In the end, you want to get the minimum value of the sum of x consecutive days. Assuming that your data is stored in the range E2:AI2 and the number of consecutive days is stored in cell A1, the function looks like this:

=MIN(BYCOL(E2:AI2,LAMBDA(col,SUM(OFFSET(col,,,,A1)))))

The MIN() part ensures that you get only the smallest sum of the array (all sums of the x consecutive values) returned. The array is simply the range in which your data is stored; it is named in the lambda argument col and consequently used by its name. In your case, you want to apply the sum function for, e.g., x = 4 consecutive days (where 4 is stored in cell A1).

However, with this simple specification, you run into the problem of offsetting beyond cells with values toward the right end of the data. This means that the last sum you get would be 81.8 (value on 31 Jan) + 3 times 0 because the cells are empty. To avoid this, you can combine your function with an IF() statement that replaces the result with an empty cell if the number of empty cells is greater than 0. The adjusted formula looks like this:

=MIN(BYCOL(E2:AI2,
LAMBDA(col,IF(COUNTIF(OFFSET(col,,,,A1),"")>0,"",SUM(OFFSET(col,,,,A1))))))

If you do not have the Microsoft 365 version, there are two approaches that would also work. However, the two approaches are a bit more tedious, especially for cases with multiple days (because the number of days can not really be set automatically; except for potentially constructing the ranges with a combination of ADDRESS() and INDIRECT()), but I would still argue a bit neater than your current specification:

=MIN(INDEX(E2:AF2+F2:AG2+G2:AH2+H2:AI2,0))

=SUMPRODUCT(MIN(E2:AF2+F2:AG2+G2:AH2+H2:AI2))

The idea regarding the ranges is the same in both scenarios, with a shift in the start and end of the range by 1 for each additional day.

Upvotes: 1

Related Questions