"Sigma" function in Excel?

**Sinstrus** · 05-19-2010, 04:59 AM

Hello everyone,

I'm new to the forum and a novice at Excel functionality. I've run into a problem recently.

So here's what I'd like to do:

I would like Excel to sum the "sigma" function 1+2*(x-1) for x = 1,2,3,...,n.
Hence, for x=1 to x=4, what I want is:
[1+2*({1}-1)]+[1+2*({2}-1)]+[1+2*({3}-1)]+[1+2*({4}-1)] = 17

Most importantly, I would like n to be a number that I directly input into another cell.
Thus, if I input the n value into A1, I want Excel to calculate 1+2*(x-1) for x = 1, 2, 3,..., A1.

Is there any way Excel can accomplish this?

**Roshan10043** · 05-19-2010, 06:12 AM

Hi, I of the simplest way would be creating your own function.

Following are the steps to do it:

Press Alt+F11 in your excel worksheet.
You will see a visual basic window. On the left hand side, you will have a Project window where all the available sheets are listed down. Right click and insert a module and paste the below mentioned code in the module: (From Function to End Function only)

Please Login or Register  to view this content.

Save the file and enable the macros by going into Tools-Macros-Security-enable macros

In the workbook now you can use your own created function named as Sigma.

format would be: =Sigma(cell reference)

**canapone** · 05-19-2010, 08:06 AM

Hi, a non VBA solution could be

=SUMPRODUCT(1+2*(ROW(INDIRECT("1:"&$A$1))-1))

In A1 your maximum value (4 in the example).

Hope it helps you.

**NBVC** · 05-19-2010, 08:15 AM

Roshan10043,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Sinstrus** · 05-19-2010, 01:05 PM

Originally Posted by CANAPONE

Hi, a non VBA solution could be

=SUMPRODUCT(1+2*(ROW(INDIRECT("1:"&$A$1))-1))

In A1 your maximum value (4 in the example).

Hope it helps you.

Thanks! This seems to work perfectly. I'll have to learn more about how it works eventually, but for now, this is great.

**squiggler47** · 05-19-2010, 02:25 PM

ermmm

anybody else notice the series?

1=1 2=4 3=9 4=16 5=25

sigma = a1^2

**squiggler47** · 05-19-2010, 05:17 PM

Hope I didn't inadvertently give the answer to what the spreadsheet was trying to figure out!

**TakiMan** · 03-21-2014, 07:55 AM

Hi,
I'm trying to come up with a similar vba solution to.... Sigma from j=0 to i of: L((i-j)/i)*b*t. Any help?

**zbor** · 03-21-2014, 08:13 AM

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

**TakiMan** · 03-21-2014, 08:53 AM

sorry about that! That was my first post and I already violated forum rules...

**w802kx** · 10-17-2014, 09:11 AM

thanks all ,I am looking for solutions of the same question.

"Sigma" function in Excel?

LinkBack

Thread Tools

Display

"Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Re: "Sigma" function in Excel?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions