Stumped...

  • Thread starter Thread starter efreedland
  • Start date Start date
E

efreedland

Can anyone suggest a way to accomplish the following summation for a
column of numbers, i.e. formula in the "Summation" columnt:

______________________________________________________________
Numbers Summation
10 10
3 13
-15 0
3 3
-7 0
8 8
-6 2
-7 0

______________________________________________________________
Thank you.
 
Assuming we are talking about columns A and B here, put this formula
in B2 :

=MAX(A2,0)

and put this one in B3 and copy it down as required:

=MAX(A3+B2,0)

Hope this helps.

Pete
 
Don, Pete,

Both of the proposed solutions work. Thank you for your help.

Best Regards,

Eduard

P.S. Don's formula needs to have M1 locked: =MAX(SUM(M$1:M2), M2, 0)
 
P.S. Don's formula needs to have M1 locked: =MAX(SUM(M$1:M2), M2, 0)
Click to expand...

Are you sure? I lose the 2 opposite the -6 when I do that.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
I will be happy to send you guys a workbook. Based on the original request,
I left m1 blank> put in this
10 10 =MAX(SUM(M1:M2),M2,0)
3 13 =MAX(SUM(M2:M3),M3,0)
-15 0 =MAX(SUM(M3:M4),M4,0)
3 3 =MAX(SUM(M4:M5),M5,0)
-7 0 =MAX(SUM(M5:M6),M6,0)
8 8 =MAX(SUM(M6:M7),M7,0)
-6 2 =MAX(SUM(M7:M8),M8,0)
-7 0 =MAX(SUM(M8:M9),M9,0)
 
Don Guillett said:
I will be happy to send you guys a workbook
Click to expand...

If I'm one of the guys Don, I don't need a workbook - your formula works
fpor me

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Don,

Thank you for the follow up.

The fact is that your formula does work for the set of numbers that
I'd originally provided. However a slight modification of the
sequence results in an incorrect result (for me. You obviously had no
way of knowing what I need without additional details from me.)

For example (A - given sequence, B - expected result, C - result
produced by your solution):
A. B. C.

10 10 10
3 13 13
-15 0 0
10 10 10
-7 3 3
8 11 8
-6 5 2
-7 0 0
4 4 4

The f(8) and f(-6) are different. Perhaps I am missing something?

Thank you for your time.
 
With these values in A1:A10
Numbers
10
3
-15
10
-7
8
-6
-7
4


Try this:

B2: =MAX(N(B1)+A2,A2,0)
Copy down as far as you need

Does that help?
***********
Regards,
Ron

XL2003, WinXP
 
This solution does work.

I believe that this is a consolidated version of the solution offered
by Pete.

This has been of great help.

Thank you everyone.

Eduard
 
Thanks....

Here's the amusing irony....
I came up with that solution this morning, but when I went to post it, I
saw posts by other people I respect and figured you already had good
solutions from them.....so I skipped posting.

***********
Regards,
Ron

XL2003, WinXP
 
Don Guillett said:
I will be happy to send you guys a workbook. Based on the original
request, I left m1 blank put in this
10 10 =MAX(SUM(M1:M2),M2,0)
3 13 =MAX(SUM(M2:M3),M3,0)
-15 0 =MAX(SUM(M3:M4),M4,0)
3 3 =MAX(SUM(M4:M5),M5,0)
-7 0 =MAX(SUM(M5:M6),M6,0)
8 8 =MAX(SUM(M6:M7),M7,0)
-6 2 =MAX(SUM(M7:M8),M8,0)
-7 0 =MAX(SUM(M8:M9),M9,0)
Click to expand...
....

OK, but why'd you need SUM?

More flexible just to use a different formula in the first row,

=MAX(M2,0)

And if these formulas were in N2:N9,

N3:
=MAX(N2+M3,0)

Fill N3 down into N4:N9.

Further, your formula fails when there are sequences of negatives that sum
to less than the preceding positive running sum. That is, with some more
lines in the table, here are the results of your formula and mine.

Val __ DG __ HG
10 __ 10 __ 10
3 __ 13 __ 13
-15 __ 0 __ 0
3 __ 3 __ 3
-7 __ 0 __ 0
8 __ 8 __ 8
-6 __ 2 __ 2
-7 __ 0 __ 0
1 __ 1 __ 1
2 __ 3 __ 3
3 __ 5 __ 6
4 __ 7 __ 10
-2 __ 2 __ 8
-2 __ 0 __ 6

Other than the OP, who knows whether this is relevant to the OP's problem,
but in general this sort of thing requires keeping cumulative sums rather
than working with just the current and previous data points.
 
Back
Top