How can perform a summation?
Is there a way to perform a summation expression in Excel? For example, I need to sum 1 + 2 + 3 + 4 + 5. I can easily type 1 through 5 in five cells and use the SUM function but what if I need to not sum 1 to 5, but rather 1 to n. Is there a summation function in Excel that could perform this calculation? -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
The formula for that is n*(n+1)/2. So if the number you want to sum till is in A1 and you want the reult in cell B1 then in cell B1 you will enter the formula =A1*(A1+1)/2 Alok "Leebo" wrote: Is there a way to perform a summation expression in Excel? For example, I need to sum 1 + 2 + 3 + 4 + 5. I can easily type 1 through 5 in five cells and use the SUM function but what if I need to not sum 1 to 5, but rather 1 to n. Is there a summation function in Excel that could perform this calculation? -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
That is assuming the numbers are in consecutive order. I know that's the way
the example stated but if your actual data isn't in consecutive order use the following formula: Assume your list of numbers is in A1:A1000 and your "n" value (how far down you want to sum) is in B1. Enter this formula in any cell: =SUM(OFFSET(A1,,,B1)) -- Regards, Dave "Leebo" wrote: Is there a way to perform a summation expression in Excel? For example, I need to sum 1 + 2 + 3 + 4 + 5. I can easily type 1 through 5 in five cells and use the SUM function but what if I need to not sum 1 to 5, but rather 1 to n. Is there a summation function in Excel that could perform this calculation? -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
FWIW,
Might be of interest. Originator of this arithmetic series formula (Gauss as a school boy): http://mathworld.wolfram.com/ArithmeticSeries.html Read the short paragraph at the end.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Leebo" wrote in message ... Is there a way to perform a summation expression in Excel? For example, I need to sum 1 + 2 + 3 + 4 + 5. I can easily type 1 through 5 in five cells and use the SUM function but what if I need to not sum 1 to 5, but rather 1 to n. Is there a summation function in Excel that could perform this calculation? -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
Thanks. Both responses are helpful. The link to the Gauss writeup was exactly what I was looking for. ::S::n=::1/2n(a1::+an) So, if I have consequtive integers from A1 to An, I don't need to key a column of numbers to add up. Instead, just list the first and last number, then use this formula. If A1 = 1, B1 = 5 C1 = A2*(A1+A2)/2 then C1 = 15 Thanks again for the help. -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
Appreciate the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Leebo" wrote in message ... Thanks. Both responses are helpful. The link to the Gauss writeup was exactly what I was looking for. ::S::n=::1/2n(a1::+an) So, if I have consequtive integers from A1 to An, I don't need to key a column of numbers to add up. Instead, just list the first and last number, then use this formula. If A1 = 1, B1 = 5 C1 = A2*(A1+A2)/2 then C1 = 15 Thanks again for the help. -- Leebo ------------------------------------------------------------------------ Leebo's Profile: http://www.excelforum.com/member.php...o&userid=26523 View this thread: http://www.excelforum.com/showthread...hreadid=397925 |
All times are GMT +1. The time now is 05:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com