Variable Sum Function

  • Thread starter Thread starter David G.
  • Start date Start date
D

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
 
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).
 
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
 
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).
Click to expand...

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 -----
 
Errata....

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

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 -----
 
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))
Click to expand...

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!
 
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!!!!
 
Back
Top