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!