3310 views

Skip to first unread message

Oct 14, 2005, 10:45:19 PM10/14/05

to

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.

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.

Oct 15, 2005, 12:18:07 AM10/15/05

to

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.

SUM and SERIESSUM, you might get a handle on it.

Jerry

Oct 15, 2005, 12:21:03 AM10/15/05

to

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

Oct 15, 2005, 9:14:10 AM10/15/05

to

The range of values are not in the worksheet itself. Let me give more

details on what I want to do.

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" <BRRamac...@discussions.microsoft.com> wrote in

message news:576916F2-4871-4844...@microsoft.com...

Oct 15, 2005, 1:51:30 PM10/15/05

to

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%,

Oct 15, 2005, 2:44:29 PM10/15/05

to

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.

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" <BRRamac...@discussions.microsoft.com> wrote in

message news:56189461-3D5A-46DD...@microsoft.com...

Oct 15, 2005, 5:09:02 PM10/15/05

to

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

Sep 26, 2021, 10:24:34 PM9/26/21

to

Sep 26, 2021, 10:25:24 PM9/26/21

to

On Saturday, October 15, 2005 at 3:09:02 PM UTC-6, B. R.Ramachandran wrote:

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu