Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
Can anyone help me solve a sum of series problem in excel?
For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
Hello Chezball, Here is the basic math equation to compute the sum of a finite series... SN = Sum of N terms N = Number of terms in the series SN=N(first term + last term)/2. Note: This formula will work as long as the average difference betweens terms remains constant. Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=564539 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
With your example, this formula gave me 590 for 4.2 weeks
Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
`sum 1 to n: base + (n-1 * delta)
What I will end up needing is how many cups of coffee from weeks 5 to 7... Hi. If Start (s) =100 Difference (d) = 25, Wk1 = 1 and wk2 = 3, Then an equation might be: =(wk2-wk1+1)*(2*s + d*(wk1 + wk2 - 2))/2 (returns 375) If you change wk1 to 5, and wk2 to 7, I get 675. -- HTH. :) Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
Nice work, that was it.
If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
First, I'll refer to an excellent source on SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html The first part calculates the number of cups for 4 weeks SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0) ))-1)*B1)) Broken down further, Rounddown(A1,0) will return 4 so: SUMPRODUCT(100+((ROW(INDIRECT("1:"&4))-1)*B1)) and ROW(INDIRECT("1:"&4)) is a means by which you can return an array of numbers, in this case 1 through 4. Check excel help for the row and indirect functions. Instead of just using Row(1:4), indirect is thrown in because you don't want a direct reference to rows 1:4. If these rows are moved or deleted or additional rows are inserted/deleted, the formula is FUBAR'd (I'm sure you've seen this when you set up a referece to cell A1, then delete all of row 1, and your formula becomes #REF!). So now: SUMPRODUCT(100+({1:4}-1)*B1)) With the numbers 1:4, I subtract 1 (because week 1 is the same as the base amount and no incremental amount should be added to week 1), and multiply by the incremental amount sold each week (25) and add 100. This will give you an array of the amount sold each week for 4 weeks (the number of whole weeks): So now we have SUMPRODUCT({100,125,150,175}) and SUMPRODUCT will total these amounts to give 550. Then, I add the fractional part by computing the number of cups at 5 weeks and multiplying by the fractional amount of 4.2 weeks (the fractional part being 0.2). ((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) This part, ((ROUNDUP(A1,0)-1)*B1+100) computes the number of cups sold for all of week 5. This is multiplied by the fractional part of cell A1 (which is 0.2) by using Mod(A1,1) (resulting in 40) and added to the result of SUMPRODUCT from above to give 590. I would point out it doesn't follow the traditional formula used to compute a series sum, but I could not remember the formula (have not used it in years and I've had a few drinks so I probably should not be trying to help anybody) Leith's formula seems like it would be simpler, but I couldn't figure out how to make it work w/o computing the fractional part separately. The best I could do is =(ROUNDUP(A1,0)*(100+(ROUNDDOWN(A1,0)*B1+100))/2)-((ROUNDDOWN(A1,0)*B1+100)*(1-MOD(A1,1))) which is only a few characters shorter than sumproduct (for me, he or some of the other experts may be able to shorten it). " wrote: Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
How many cups of coffee total will I have sold them in say 4.2 weeks.
Hi. I believe the shorter recurrence equation for this particular problem is: =12.5*n*(n + 7) when n=4.2, it returns 588. I believe it's a little different than 590 because the 200 in .2*200 hasn't occurred yet. Hope I said that correctly. :0 -- Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
Thank you for this one as well.
I am still bothered by the difference this is producing. For instance, if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Why is this again? And really, is it correct if it isn't producing the correct answer? Dana DeLouis wrote: How many cups of coffee total will I have sold them in say 4.2 weeks. Hi. I believe the shorter recurrence equation for this particular problem is: =12.5*n*(n + 7) when n=4.2, it returns 588. I believe it's a little different than 590 because the 200 in .2*200 hasn't occurred yet. Hope I said that correctly. :0 -- Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
I am still bothered by the difference this is producing. For instance,
if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Hi. I don't think either answer is wrong. The difference is that you are estimating the midpoint by using a straight line. If your total sales from the beginning were a straight line, then from your description above, week 3 would be 225+125 = 350. Week 3.0 is 375. If you plot your output, you will see that the output is not a straight line. That's where the small differences are coming from. 1 100 2 225 3 375 4 550 5 750 6 975 7 1225 As a curious side note, we know that the difference between the recurrence equation and your linear interpolation equation is fixed at say...the midpoint. The total sales (s) at an integer value week (x) is given by: s(x) = 12.5*x*(x+7) Your midpoint estimate: ( s(x) + s(x+1) )/2 Recurrence midpoint s(x+.5) If you subtract the two equations, you get a constant 3.125. Therefore, a linear interpolation will always be 3.125 higher at the midpoint between integers (this specific problem). We can check your data from above... 159.375 + 3.125 = 162.5 -- HTH. :) Dana DeLouis "To understand recurrence, one must first understand recurrence." wrote in message oups.com... Thank you for this one as well. I am still bothered by the difference this is producing. For instance, if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Why is this again? And really, is it correct if it isn't producing the correct answer? Dana DeLouis wrote: How many cups of coffee total will I have sold them in say 4.2 weeks. Hi. I believe the shorter recurrence equation for this particular problem is: =12.5*n*(n + 7) when n=4.2, it returns 588. I believe it's a little different than 590 because the 200 in .2*200 hasn't occurred yet. Hope I said that correctly. :0 -- Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
If you graph your cumulative values for each week, is the graph linear or
curved? For me it is curved. But your analysis gives a linear estimate (interpolation) between the two end points. Dana's reflects the actual curve, so your assumption that 1.5 should be 162.5 would not be correct. -- Regards, Tom Ogilvy " wrote: Thank you for this one as well. I am still bothered by the difference this is producing. For instance, if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Why is this again? And really, is it correct if it isn't producing the correct answer? Dana DeLouis wrote: How many cups of coffee total will I have sold them in say 4.2 weeks. Hi. I believe the shorter recurrence equation for this particular problem is: =12.5*n*(n + 7) when n=4.2, it returns 588. I believe it's a little different than 590 because the 200 in .2*200 hasn't occurred yet. Hope I said that correctly. :0 -- Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
sum of series problem.
Great!
Thank you for your explanation. That makes sense. I appreciate everyones input into this. Dana DeLouis wrote: I am still bothered by the difference this is producing. For instance, if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Hi. I don't think either answer is wrong. The difference is that you are estimating the midpoint by using a straight line. If your total sales from the beginning were a straight line, then from your description above, week 3 would be 225+125 = 350. Week 3.0 is 375. If you plot your output, you will see that the output is not a straight line. That's where the small differences are coming from. 1 100 2 225 3 375 4 550 5 750 6 975 7 1225 As a curious side note, we know that the difference between the recurrence equation and your linear interpolation equation is fixed at say...the midpoint. The total sales (s) at an integer value week (x) is given by: s(x) = 12.5*x*(x+7) Your midpoint estimate: ( s(x) + s(x+1) )/2 Recurrence midpoint s(x+.5) If you subtract the two equations, you get a constant 3.125. Therefore, a linear interpolation will always be 3.125 higher at the midpoint between integers (this specific problem). We can check your data from above... 159.375 + 3.125 = 162.5 -- HTH. :) Dana DeLouis "To understand recurrence, one must first understand recurrence." wrote in message oups.com... Thank you for this one as well. I am still bothered by the difference this is producing. For instance, if week 1 is 100 and week 2 is 225, then week 1.5 should be 162.5 instead it is shows 159.375. Why is this again? And really, is it correct if it isn't producing the correct answer? Dana DeLouis wrote: How many cups of coffee total will I have sold them in say 4.2 weeks. Hi. I believe the shorter recurrence equation for this particular problem is: =12.5*n*(n + 7) when n=4.2, it returns 588. I believe it's a little different than 590 because the 200 in .2*200 hasn't occurred yet. Hope I said that correctly. :0 -- Dana DeLouis Windows XP, Office 2003 wrote in message ups.com... Nice work, that was it. If you have a minute, I would love it if you could parse your answer. I am a pretty basic excel user right now, and it would be neat to get a better understanding of it's language/formulas. Thanks again. JMB wrote: With your example, this formula gave me 590 for 4.2 weeks Week1: 100 Week2: 125 Week3: 150 Week4: 175 Week5: 200 Total = (100+125+150+175+(0.2*200) = 590 Where A1 is the number of weeks and B1=25 (each weeks increase) =SUMPRODUCT(100+((ROW(INDIRECT("1:"&ROUNDDOWN(A1,0 )))-1)*B1))+((ROUNDUP(A1,0)-1)*B1+100)*MOD(A1,1) Does this give the results that you are looking for? " wrote: Can anyone help me solve a sum of series problem in excel? For instance: `sum 1 to n: base + (n-1 * delta) Or more specifically: Say I have x customers at week 1, say 100, and each week I increase my customers by y, say 25. Week 1 I have 100 Week 2 I have 125 And each week I sell them a cup of coffee. How many cups of coffee total will I have sold them in say 4.2 weeks. Week 1 100 customer = 100 cups this week, 100 total Week 2 125 customer = 125 cups this week, 225 total Week 3 150 customer = 150 cups this week, 375 total What I will end up needing is how many cups of coffee from weeks 5 to 7, which I could simply subtract, but could also be made a part of the series, which I can't figure out in excel! ;-) Any help? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Series Values - Please Help | Charts and Charting in Excel | |||
Problem formatting series in VBA | Excel Programming | |||
Pivot Series problem | Charts and Charting in Excel | |||
Series Problem | Charts and Charting in Excel | |||
Q. Pie Chart problem. Want to add series, from another worksheet. | Excel Programming |