Find lowest sum value of all possible sum combinations

[Jasperverdam]

Hi All,

I have a list of values, both positives and negatives and I would like to find the lowest point that can be obtained by summing any of the possible sum combinations of that list. However they do have to stay in sequence. For example:

A1: -3
A2: 5
A3: -4
A4: -1
A5: 2

Adding -3, -4 and -1 is not correct as they are not in sequence (it would be +2 before it reaches cell A3 (-3+5))

In this case -5 would be the lowest point, but my lists are rather large so I am looking for a quicker way to do it rather than observing. It pretty much is a MAX function of (A1,A2,A3,A4,A5;SUM(A1:A2) up to SUM(A1:A5), but also SUM(A2:A3) up to SUM(A2:A5) all the way up to SUM(A4:A5).

Hope this makes sence!

BR,
Jasper Verdam

 

[Jasperverdam]

Hi shg,

Boom, this is perfect, exactly what I am looking for!

Now ideally I get some help implementing it haha.

What is idea behind D1 and E1?

And is this formula {=Kadane(-A1:A20, TRUE) * {-1,1,1}} in Cell C1 with the code pasted in VBA?
I'm a bit confused as to how to manage it all.

 

[shg]

What is idea behind D1 and E1?

It tells you the start and end indices where the max (negative) sum is found

And is this formula {=Kadane(-A1:A20, TRUE) * {-1,1,1}} in Cell C1 with the code pasted in VBA?

Select C1:E1, paste the formula in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

 

[Jasperverdam]

Works like a charm!

Many thanks

 

[shg]

You're welcome.

 

[Jasperverdam]

Hi shg,

Let me try to test your skills one more time
Do you know if a kadaneif somehow is possible?

So currently I have year in column A, month number in column B, and the values in column C. Since I have 10 years I have 120 cells for which I need to use the Kadane (and several different files that need to use the same process), it's a lot of work to constantly change the cells to which to relate the Kadane formula. All the cells for 1 month are sorted and together i.e. 2017 12 is only from cells C1 to C20 and the combination 2017-12 won't show up anywhere else in the file.

So I somehow would like to have a formula that says if A is 2017, B is 12 then kadane of all those cells.

Any help is much appreciated!

 

[Jasperverdam]

Ah I just realized I can just use your Kadane code in combination with IF(AND haha!

No need to comment anymore, all sorted.