How to Sum the Largest Negative and Positive Total in One Column Question?

MXL

Board Regular
Joined
Jun 15, 2010
Messages
67
Hi,

I wanted to know how to sum the longest negative and positive sum within one column with these 5 data points as the example -1-6+10-5+3 , ill show example below.

-1
-6
10
-5
3
largest negative sum = -7
largest positive sum = +3

in this column, you can see the largest negative sum was -7, -1-6=-7, at no other point did the negative sum throughout the -1-6+10-5+3 sum go larger than -7.

in the same column, the largest positive sum, -1-6+10=+3, at no other point did the positive sum throughout the -1-6+10-5+3 sum go larger than +3,,,

is there a way to have two formulas that can sum the largest negative sum -7 and the largest positive sum +3 in this column of 5 numbers?

thanks!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If it is always a fixed set of 5 cells then you could simplify this formula slightly.
Book1
AB
2-1
3-6
410
5-5
63
7Largest -ve-7
8Largest +ve3
Sheet1
Cell Formulas
RangeFormula
B7B7=AGGREGATE(15,6,SUBTOTAL(9,OFFSET($A$2,,,ROW($A$2:$A$6)-ROW($A$2)+1,1)),1)
B8B8=AGGREGATE(14,6,SUBTOTAL(9,OFFSET($A$2,,,ROW($A$2:$A$6)-ROW($A$2)+1,1)),1)
 
Upvote 0
If it is always a fixed set of 5 cells then you could simplify this formula slightly.
Book1
AB
2-1
3-6
410
5-5
63
7Largest -ve-7
8Largest +ve3
Sheet1
Cell Formulas
RangeFormula
B7B7=AGGREGATE(15,6,SUBTOTAL(9,OFFSET($A$2,,,ROW($A$2:$A$6)-ROW($A$2)+1,1)),1)
B8B8=AGGREGATE(14,6,SUBTOTAL(9,OFFSET($A$2,,,ROW($A$2:$A$6)-ROW($A$2)+1,1)),1)
Geez there's actually some odd behaviour with that formula, it's taking me a bit to get my head around it. Why does AGGREGATE force SUBTOTAL to work with arrays? Why does Excel's "Evaluate Formula" show a bunch of errors within SUBTOTAL (pic below), only to correctly calculate each rolling sum?

1599607687393.png
 
Upvote 0
Why does AGGREGATE force SUBTOTAL to work with arrays?
Other functions do as well, MIN and MAX or SMALL and LARGE would have worked here. The benefit of AGGREGATE is that it doesn't require array confirmation.

Why does Excel's "Evaluate Formula" show a bunch of errors within SUBTOTAL (pic below), only to correctly calculate each rolling sum?
Subtotal only works with ranges, not arrays. Each error in the array is actually a range produced by offset, if you type =A1:A2 into a cell it will give you an error because there is nothing to manipulate the values of the 2 cells. The errors that you see when you evaluate are caused by a similar principle. There are a number of other functions that exhibit similar behaviour when offset is used to generate multiple ranges (sumifs, countifs, etc).
 
Upvote 0
Other functions do as well, MIN and MAX or SMALL and LARGE would have worked here. The benefit of AGGREGATE is that it doesn't require array confirmation.


Subtotal only works with ranges, not arrays. Each error in the array is actually a range produced by offset, if you type =A1:A2 into a cell it will give you an error because there is nothing to manipulate the values of the 2 cells. The errors that you see when you evaluate are caused by a similar principle. There are a number of other functions that exhibit similar behaviour when offset is used to generate multiple ranges (sumifs, countifs, etc).
Amazing, thank you! Agreed, not having to hit CSE is definitely an advantage and yes I guess I have seen similar behaviour when nesting other formulas. It's hard for me to guess when it will work and when it won't... I'm keen to find the pattern.

Thanks for taking the time to answer my questions and well done on the neat solution to the post.
 
Upvote 0
Why not just simple worksheet functions like:
MrExcel_How to Sum the Largest Negative and Positive Total in One Column Question.xlsm
ABCD
1SeriesCumulative sumLargest -veLargest +ve
2-1-1-1-1
3-6-7-7-1
4103-73
5-5-2-73
631-73
Sheet2
Cell Formulas
RangeFormula
B2B2=A2
C2C2=A2
D2D2=A2
B3:B6B3=A3+B2
C3:C6C3=IF(B3<C2,B3,C2)
D3:D6D3=IF(B3>D2,B3,D2)

On 1000 rows of data this is about 8x faster than the AGGREGATE solution.
 
Upvote 0
thank you for your responses, much appreciated, well i wanted it only for one column to conserve room....
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top