Sigma Function?

Does Excel have a "sigma" function . . . i.e. I want to sum all the values
of a formula over a range of values (z = 1 to n). Ideally, I would like to
do this within a single function, rather than externalize the range of
values in the spreadsheet and then sum those values.

Any help or suggestions would be appreciated.

You don't give much in the way of specifics, but if you check Help for
SUM and SERIESSUM, you might get a handle on it.

Jerry

Maria Garcao wrote:

> Does Excel have a "sigma" function . . . i.e. I want to sum all the values
> of a formula over a range of values (z = 1 to n). Ideally, I would like to
> do this within a single function, rather than externalize the range of
> values in the spreadsheet and then sum those values.
>
> Any help or suggestions would be appreciated.

Hi,

If the range of values are say in A1:An, and you want to calculate Sigma
f(Ai) for i = 1 to n where 'f' is a function (without having to calculate the
individual values of f(A1), f(A2)....f(An) and summing them up), you can use
an array formula as follows:.

In a destination cell enter the formula s
=SUM(f(A1:An)) and press CTRL-SHIFT-ENTER.

For example, if you want to calculate the sum of 2*ln(Ai) + 3*sqrt(Ai) + 4
forthe contents of cells A1....A10, the formula will be
=SUM(2*ln(A1:A10) + 3*SQRT(A1:A10) + 4) confirmed with CTRL-SHIFT-ENTER.

Regards,
B. R. Ramachandran


"Maria Garcao" wrote:

> Does Excel have a "sigma" function . . . i.e. I want to sum all the values
> of a formula over a range of values (z = 1 to n). Ideally, I would like to
> do this within a single function, rather than externalize the range of
> values in the spreadsheet and then sum those values.
>
> Any help or suggestions would be appreciated.
>
>
>

The range of values are not in the worksheet itself. Let me give more
details on what I want to do.

My problem: I'm trying to calculate the number of rows that will be stored
in a data warehouse fact table over a period of time. My assumption is that
I will be starting with "X" number of rows that will be stored the first
week, and that every week we will be adding another bunch of "X" rows, but
"X" will be growing by approximately 1% every week.

For example, lets say "X" is 1,000,000 rows and I want to calculate how many
rows will be stored over 6 weeks.

Week 1: 1,000,000
Week 2: 1,010,000
Week 3: 1,020,100
Week 4: 1,030,301
Week 5: 1,040,604
Week 6: 1,051,010

So my sum after 6 weeks would be: 6,152,015

I have two numbers stored in two cells of the worksheet:

Cell A1 = X = "starting" number of rows
Cell A2 = Y = number of weeks to calculate for

So the formula that I want to sum is "=INT(A1*(POWER, 1.01, n-1))", where n
ranges from 1 to A2.

I don't want populate "n" number of cells and then just sum them up because
"n" can get quite large, and I want to quickly be able to model the effects
of changing the value of "n" for different fact tables.

Hopeully this sheds more light on exactly what I'm trying to do.

"B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
message news:576916F2-4871-4844-88B8-AEF2EFCF6D9D@microsoft.com...
> Hi,
>
> If the range of values are say in A1:An, and you want to calculate Sigma
> f(Ai) for i = 1 to n where 'f' is a function (without having to calculate
the
> individual values of f(A1), f(A2)....f(An) and summing them up), you can
use
> an array formula as follows:.
>
> In a destination cell enter the formula s
> =SUM(f(A1:An)) and press CTRL-SHIFT-ENTER.
>
> For example, if you want to calculate the sum of 2*ln(Ai) + 3*sqrt(Ai) + 4
> forthe contents of cells A1....A10, the formula will be
> =SUM(2*ln(A1:A10) + 3*SQRT(A1:A10) + 4) confirmed with CTRL-SHIFT-ENTER.
>
> Regards,
> B. R. Ramachandran
>
>
> "Maria Garcao" wrote:
>
> > Does Excel have a "sigma" function . . . i.e. I want to sum all the
values
> > of a formula over a range of values (z = 1 to n). Ideally, I would like
to
> > do this within a single function, rather than externalize the range of
> > values in the spreadsheet and then sum those values.
> >
> > Any help or suggestions would be appreciated.
> >
> >
> >

Hi,

The formula is, 100*I/G*((1+G/100)^W-1), where I is the starting number, G
is growth in percentage, W is the number of weeks. So when A1 and B1 contain
the starting number and number of weeks respectively, and the weekly growth
is 1%,

=100*A1/1*((1.01)^B1-1)

If you want you can place the growth percent in another cell (say C1, format
the cell as a number and not percent) and the formula will be

=100*A1/C1*((1+C1/100)^B1-1)

Note that you might want to round off the result to the nearest integer as,
=INT(100*A1/C1*((1+C1/100)^B1-1)).

Regards,
B. R. Ramachandran

Remember the result

f A1 and B1 contain the starting numberand the number of weeks respectively,
and if the growth is 1%,
"Maria Garcao" wrote:

> The range of values are not in the worksheet itself. Let me give more
> details on what I want to do.
>
> My problem: I'm trying to calculate the number of rows that will be stored
> in a data warehouse fact table over a period of time. My assumption is that
> I will be starting with "X" number of rows that will be stored the first
> week, and that every week we will be adding another bunch of "X" rows, but
> "X" will be growing by approximately 1% every week.
>
> For example, lets say "X" is 1,000,000 rows and I want to calculate how many
> rows will be stored over 6 weeks.
>
> Week 1: 1,000,000
> Week 2: 1,010,000
> Week 3: 1,020,100
> Week 4: 1,030,301
> Week 5: 1,040,604
> Week 6: 1,051,010
>
> So my sum after 6 weeks would be: 6,152,015
>
> I have two numbers stored in two cells of the worksheet:
>
> Cell A1 = X = "starting" number of rows
> Cell A2 = Y = number of weeks to calculate for
>
> So the formula that I want to sum is "=INT(A1*(POWER, 1.01, n-1))", where n
> ranges from 1 to A2.
>
> I don't want populate "n" number of cells and then just sum them up because
> "n" can get quite large, and I want to quickly be able to model the effects
> of changing the value of "n" for different fact tables.
>
> Hopeully this sheds more light on exactly what I'm trying to do.
>
> "B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
> message news:576916F2-4871-4844-88B8-AEF2EFCF6D9D@microsoft.com...
> > Hi,
> >
> > If the range of values are say in A1:An, and you want to calculate Sigma
> > f(Ai) for i = 1 to n where 'f' is a function (without having to calculate
> the
> > individual values of f(A1), f(A2)....f(An) and summing them up), you can
> use
> > an array formula as follows:.
> >
> > In a destination cell enter the formula s
> > =SUM(f(A1:An)) and press CTRL-SHIFT-ENTER.
> >
> > For example, if you want to calculate the sum of 2*ln(Ai) + 3*sqrt(Ai) + 4
> > forthe contents of cells A1....A10, the formula will be
> > =SUM(2*ln(A1:A10) + 3*SQRT(A1:A10) + 4) confirmed with CTRL-SHIFT-ENTER.
> >
> > Regards,
> > B. R. Ramachandran
> >
> >
> > "Maria Garcao" wrote:
> >
> > > Does Excel have a "sigma" function . . . i.e. I want to sum all the
> values
> > > of a formula over a range of values (z = 1 to n). Ideally, I would like
> to
> > > do this within a single function, rather than externalize the range of
> > > values in the spreadsheet and then sum those values.
> > >
> > > Any help or suggestions would be appreciated.
> > >
> > >
> > >
>
>
>

Thanks! This seems to do exactly what I want. It's going to take me the
rest of the weekend to break it down to understand exactly how it works, but
at least I'll have something to work with once I get back in the office on
Monday.

"B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
message news:56189461-3D5A-46DD-85BA-E16036A13BC0@microsoft.com...
> Hi,
>
> The formula is, 100*I/G*((1+G/100)^W-1), where I is the starting number, G
> is growth in percentage, W is the number of weeks. So when A1 and B1
contain
> the starting number and number of weeks respectively, and the weekly
growth
> is 1%,
>
> =100*A1/1*((1.01)^B1-1)
>
> If you want you can place the growth percent in another cell (say C1,
format
> the cell as a number and not percent) and the formula will be
>
> =100*A1/C1*((1+C1/100)^B1-1)
>
> Note that you might want to round off the result to the nearest integer
as,
> =INT(100*A1/C1*((1+C1/100)^B1-1)).
>
> Regards,
> B. R. Ramachandran
>
> Remember the result
>
> f A1 and B1 contain the starting numberand the number of weeks
respectively,
> and if the growth is 1%,
> "Maria Garcao" wrote:
>
> > The range of values are not in the worksheet itself. Let me give more
> > details on what I want to do.
> >
> > My problem: I'm trying to calculate the number of rows that will be
stored
> > in a data warehouse fact table over a period of time. My assumption is
that
> > I will be starting with "X" number of rows that will be stored the first
> > week, and that every week we will be adding another bunch of "X" rows,
but
> > "X" will be growing by approximately 1% every week.
> >
> > For example, lets say "X" is 1,000,000 rows and I want to calculate how
many
> > rows will be stored over 6 weeks.
> >
> > Week 1: 1,000,000
> > Week 2: 1,010,000
> > Week 3: 1,020,100
> > Week 4: 1,030,301
> > Week 5: 1,040,604
> > Week 6: 1,051,010
> >
> > So my sum after 6 weeks would be: 6,152,015
> >
> > I have two numbers stored in two cells of the worksheet:
> >
> > Cell A1 = X = "starting" number of rows
> > Cell A2 = Y = number of weeks to calculate for
> >
> > So the formula that I want to sum is "=INT(A1*(POWER, 1.01, n-1))",
where n
> > ranges from 1 to A2.
> >
> > I don't want populate "n" number of cells and then just sum them up
because
> > "n" can get quite large, and I want to quickly be able to model the
effects
> > of changing the value of "n" for different fact tables.
> >
> > Hopeully this sheds more light on exactly what I'm trying to do.
> >
> > "B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
> > message news:576916F2-4871-4844-88B8-AEF2EFCF6D9D@microsoft.com...
> > > Hi,
> > >
> > > If the range of values are say in A1:An, and you want to calculate
Sigma
> > > f(Ai) for i = 1 to n where 'f' is a function (without having to
calculate
> > the
> > > individual values of f(A1), f(A2)....f(An) and summing them up), you
can
> > use
> > > an array formula as follows:.
> > >
> > > In a destination cell enter the formula s
> > > =SUM(f(A1:An)) and press CTRL-SHIFT-ENTER.
> > >
> > > For example, if you want to calculate the sum of 2*ln(Ai) + 3*sqrt(Ai)
+ 4
> > > forthe contents of cells A1....A10, the formula will be
> > > =SUM(2*ln(A1:A10) + 3*SQRT(A1:A10) + 4) confirmed with
CTRL-SHIFT-ENTER.
> > >
> > > Regards,
> > > B. R. Ramachandran
> > >
> > >
> > > "Maria Garcao" wrote:
> > >
> > > > Does Excel have a "sigma" function . . . i.e. I want to sum all the
> > values
> > > > of a formula over a range of values (z = 1 to n). Ideally, I would
like
> > to
> > > > do this within a single function, rather than externalize the range
of
> > > > values in the spreadsheet and then sum those values.
> > > >
> > > > Any help or suggestions would be appreciated.
> > > >
> > > >
> > > >
> >
> >
> >

Hi,

Weekly additions (Here, x stands for (1+G/100); e.g., 1.01 if G is 1%)

Week 1 I
Week 2 I*x
Week 3 I*x^2.
..
Week n I*x^(n-1)

So cumulative totals each week (this is what you want) will be:

Week 1 I
Week 2 I + I*x)) = I*(1+x)
Week 3 I + I*x + I*x^2 = I*(1+x+^2)
..
..
Week n I*(1+x+x^2+............+ x^(n-1)

This is a gemometric series and the sum is given by the following formula,
Sum = I*(x^n - 1)/(x-1).
Remember that x = 1+G/100; so the sum is,
= I*((1+G/100)^n - 1)/(1+G/100-1)
= 100*I/G*((1+G/100)^n- 1)
Note that 'n' in this formula is the week number.
So, as in your example, if I=1,000,000, G= 1%, and W=6
=100*1000,000*(1.01^6-1)
=6152015

Regards,
B. R. Ramachandran









"Maria Garcao" wrote:

> Thanks! This seems to do exactly what I want. It's going to take me the
> rest of the weekend to break it down to understand exactly how it works, but
> at least I'll have something to work with once I get back in the office on
> Monday.
>
> "B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
> message news:56189461-3D5A-46DD-85BA-E16036A13BC0@microsoft.com...
> > Hi,
> >
> > The formula is, 100*I/G*((1+G/100)^W-1), where I is the starting number, G
> > is growth in percentage, W is the number of weeks. So when A1 and B1
> contain
> > the starting number and number of weeks respectively, and the weekly
> growth
> > is 1%,
> >
> > =100*A1/1*((1.01)^B1-1)
> >
> > If you want you can place the growth percent in another cell (say C1,
> format
> > the cell as a number and not percent) and the formula will be
> >
> > =100*A1/C1*((1+C1/100)^B1-1)
> >
> > Note that you might want to round off the result to the nearest integer
> as,
> > =INT(100*A1/C1*((1+C1/100)^B1-1)).
> >
> > Regards,
> > B. R. Ramachandran
> >
> > Remember the result
> >
> > f A1 and B1 contain the starting numberand the number of weeks
> respectively,
> > and if the growth is 1%,
> > "Maria Garcao" wrote:
> >
> > > The range of values are not in the worksheet itself. Let me give more
> > > details on what I want to do.
> > >
> > > My problem: I'm trying to calculate the number of rows that will be
> stored
> > > in a data warehouse fact table over a period of time. My assumption is
> that
> > > I will be starting with "X" number of rows that will be stored the first
> > > week, and that every week we will be adding another bunch of "X" rows,
> but
> > > "X" will be growing by approximately 1% every week.
> > >
> > > For example, lets say "X" is 1,000,000 rows and I want to calculate how
> many
> > > rows will be stored over 6 weeks.
> > >
> > > Week 1: 1,000,000
> > > Week 2: 1,010,000
> > > Week 3: 1,020,100
> > > Week 4: 1,030,301
> > > Week 5: 1,040,604
> > > Week 6: 1,051,010
> > >
> > > So my sum after 6 weeks would be: 6,152,015
> > >
> > > I have two numbers stored in two cells of the worksheet:
> > >
> > > Cell A1 = X = "starting" number of rows
> > > Cell A2 = Y = number of weeks to calculate for
> > >
> > > So the formula that I want to sum is "=INT(A1*(POWER, 1.01, n-1))",
> where n
> > > ranges from 1 to A2.
> > >
> > > I don't want populate "n" number of cells and then just sum them up
> because
> > > "n" can get quite large, and I want to quickly be able to model the
> effects
> > > of changing the value of "n" for different fact tables.
> > >
> > > Hopeully this sheds more light on exactly what I'm trying to do.
> > >
> > > "B. R.Ramachandran" <BRRamachandran@discussions.microsoft.com> wrote in
> > > message news:576916F2-4871-4844-88B8-AEF2EFCF6D9D@microsoft.com...
> > > > Hi,
> > > >
> > > > If the range of values are say in A1:An, and you want to calculate
> Sigma
> > > > f(Ai) for i = 1 to n where 'f' is a function (without having to
> calculate
> > > the
> > > > individual values of f(A1), f(A2)....f(An) and summing them up), you
> can
> > > use
> > > > an array formula as follows:.
> > > >
> > > > In a destination cell enter the formula s
> > > > =SUM(f(A1:An)) and press CTRL-SHIFT-ENTER.
> > > >
> > > > For example, if you want to calculate the sum of 2*ln(Ai) + 3*sqrt(Ai)
> + 4
> > > > forthe contents of cells A1....A10, the formula will be
> > > > =SUM(2*ln(A1:A10) + 3*SQRT(A1:A10) + 4) confirmed with
> CTRL-SHIFT-ENTER.
> > > >
> > > > Regards,
> > > > B. R. Ramachandran
> > > >
> > > >
> > > > "Maria Garcao" wrote:
> > > >
> > > > > Does Excel have a "sigma" function . . . i.e. I want to sum all the
> > > values
> > > > > of a formula over a range of values (z = 1 to n). Ideally, I would
> like
> > > to
> > > > > do this within a single function, rather than externalize the range
> of
> > > > > values in the spreadsheet and then sum those values.
> > > > >
> > > > > Any help or suggestions would be appreciated.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>