Variable Sum Function

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
 
J

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).
 
D

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
 
J

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 -----
 
J

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 -----
 
D

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!
 
D

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top