Variable Sum Function
 

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

Variable Sum Function
 

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




In article ,
David G. wrote:

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

Variable Sum Function
 

David G. wrote:
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*(--B135)

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

Variable Sum Function
 

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

Variable Sum Function
 

On Dec 26, 7:06 pm, David G. wrote:
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 -----

On Dec 26, 7:06*pm, David G. wrote:
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

Variable Sum Function
 

Errata....

On Dec 26, 11:14 pm, I wrote:
=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 -----

On Dec 26, 11:14*pm, joeu2004 wrote:
On Dec 26, 7:06 pm, David G. wrote:

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

On Dec 26, 7:06*pm, David G. wrote:



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- Hide quoted text -

- Show quoted text -

Variable Sum Function
 

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.
--
David Biddulph

"smartin" wrote in message
...
Perhaps on rows 2 and fill down is

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

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

David G. wrote:
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

Variable Sum Function
 

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

David Biddulph wrote:
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.
--
David Biddulph

"smartin" wrote in message
...
Perhaps on rows 2 and fill down is

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

Variable Sum Function
 

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

On Dec 26, 11:14 pm, I wrote:
=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!

Variable Sum Function
 

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