Variable Sum Function

[David G.]

Hi! Is there a formula that provides the following?:

I have two sequential totals, say 35, & 49. In the example below I want
column B to sum column A until it reaches condition 1 (35). That would be
B2. I then want B3 to (a)begin sum at A3 & (b)add the difference of
condition 1 minus B2. In B5 the sequence will repeat.

The trick is A1 is variable, that is, A2 could have 15, meaing that the
condition 1 will be met on B3 or another subsequent cell. I want this to
roll as the data is entered in A.

I hope this isn't oo confusing

A B
1 15 15
2 32 47
3 10 22
4 25 47
5 4 6
6 13 19

 

[JE McGimpsey]

Perhaps I'm being dense, but I'm having trouble following your example.

The 47 in B2 is greater than your first criterion, though the "excess"
(47 - 35 = 12) is then carried over in B3 (10 + 12 = 22). Yet the 47 in
B4 is *less* than your second criterion yet a "carry-over" of 2 appears
to be happening in B5.

Seems to me that to be consistent with B2, the result in B5 should be
51, and B6 should include the carryover of 2 (e.g., 15).

 

[smartin]

Hi! Is there a formula that provides the following?:

I have two sequential totals, say 35, & 49. In the example below I want
column B to sum column A until it reaches condition 1 (35). That would be
B2. I then want B3 to (a)begin sum at A3 & (b)add the difference of
condition 1 minus B2. In B5 the sequence will repeat.

The trick is A1 is variable, that is, A2 could have 15, meaing that the
condition 1 will be met on B3 or another subsequent cell. I want this to
roll as the data is entered in A.

I hope this isn't oo confusing

A B
1 15 15
2 32 47
3 10 22
4 25 47
5 4 6
6 13 19

Perhaps on rows 2 and fill down is

=B1+A2-35*(--B1>35)

Though I get different results in B5 and B6 (typo in your example?)

 

[David G.]

Sorry about the confusion. The table was not consistent with what I tried to
explain.

What I have in column A are units produced per day. Column B is a
cummulative sum. I want the cummlative (Column B cells) to count up to
deliveries. When the accumulation reaches the 1st quantity to be delivered
(A8=35) The cell zeroes out, adds any excess and restarts the accumulation
for the second delivery (A9=49).

The corrected table should look like this:

A B
1 15 15
2 32 47
3 10 22
4 25 47
5 4 51
6 13 15
7
8 35
9 49

 

[joeu2004]

I want the cummlative (Column B cells) to count up
to deliveries. When the accumulation reaches the
1st quantity to be delivered (A8=35) The cell zeroes
out, adds any excess and restarts the accumulation
for the second delivery (A9=49).

There may be a more efficient way to do this. But
for one solution, enter the following into B2 and
copy down:

=B1 + A2
- IF(SUM($A$1:A1)<$B$9, $B$8*(B1>=$B$8), $B$9*(B1>=$B$9))

(I assume that B1 is simply =A1.)

Note: Be sure this gives the desired results when
A4 is 13 and A5 is 14. You will understand why when
you try it.


----- original posting -----

 

[joeu2004]

Errata....

=B1 + A2
- IF(SUM($A$1:A1)<$B$9, $B$8*(B1>=$B$8), $B$9*(B1>=$B$9))

Change $B$8 and $B$9 to $A$8 and $A$9 respectively.
I did not read your text carefully, and in my view
of your posting, the constants (35 and 49) lined up
with column B. My bad!


----- original posting -----

 

[David Biddulph]

Would you like to explain why you've got a double unary minus? If you are
trying to convert from boolean to a number, any arithmetic operation will do
it. Two negations is a way if you're not doing any other arithmetic, but as
you've got a multiplication I can't see the need for the double negation.

 

[smartin]

Point well taken, it was just force of habit and lazy thinking on my part.

 

[David G.]

It worked just fine. However, if A9 is lower than B1, it gets all out of
whack. Any suggestions? Also, can I have more than two delivery quantities?

Thanks!!!!

Errata....

=B1 + A2
- IF(SUM($A$1:A1)<$B$9, $B$8*(B1>=$B$8), $B$9*(B1>=$B$9))

Change $B$8 and $B$9 to $A$8 and $A$9 respectively.
I did not read your text carefully, and in my view
of your posting, the constants (35 and 49) lined up
with column B. My bad!

 

[David G.]

It works fine. However if A9 is lower than B1 (46 or less), the formula gets
out of whack. Any suggestions? Also, can I have more than two deliveries?

Thanks!!!!