Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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?) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use VBA variable in COUNTIF function? | Excel Worksheet Functions | |||
Variable function | Excel Discussion (Misc queries) | |||
Variable function | Excel Discussion (Misc queries) | |||
Variable function | Excel Discussion (Misc queries) | |||
using sheet-variable in function | Excel Worksheet Functions |