CODEWITHSUNDEEP
excelexcel-formula
PPA
PPA

Reputation: 25

Greedy sum in Excel

I have a problem to create formula (not macro, this is easy) implementing "greedy" sum. "Greedy" sum adds values in a row up to specified limit. For example: if limit is 3 and in row I have values 1;1;2 => formula should return 2 (1 + 1, as adding 2 will cross the limit)

Can you help me?

Upvotes: 1

Views: 219

Answers (2)

XOR LX
XOR LX

Reputation: 7762

Assuming values in A2:C2 and threshold in A1, array formula**:

=IF(A2>A1,"No Solution",IF(SUM(A2:C2)<=A1,SUM(A2:C2),SUM(A2:INDEX(A2:C2,MATCH(TRUE,MMULT(A2:C2,0+(COLUMN(A2:C2)>=TRANSPOSE(COLUMN(A2:C2))))>A1,0)-1))))

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Upvotes: 3

Matt Cremeens
Matt Cremeens

Reputation: 5151

You could try to use the INDEX function to set limits on a range you enter. Suppose you had the numbers

1; 1; 5; 2; 1; 1; 1; 1

in cells F1:M1, then the formula

=SUM(INDEX(F1:M1,1,1):INDEX(F1:M1,1,3))

would return (1 + 1 + 5); that is, what is in the first cell in the range to one less the 3rd cell in the range.

Upvotes: 0

Related Questions