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!

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

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?

"[email protected]" 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!
>
>

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?
>
> "[email protected]" 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!
> >
> >

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


"[email protected]" 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?
> >
> > "[email protected]" 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!
> > >
> > >
>
>

> 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


<[email protected]> wrote in message
news:[email protected]...
> 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?
>>
>> "[email protected]" 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!
>> >
>> >
>

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
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >>
> >> "[email protected]" 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!
> >> >
> >> >
> >

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>> >>
>> >> "[email protected]" 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!
>> >> >
>> >> >
>> >
>

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


"[email protected]" 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
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >>
> > >> "[email protected]" 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!
> > >> >
> > >> >
> > >
>
>

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


<[email protected]> wrote in message
news:[email protected]...
> 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!
>