need calculation to add sums starting one less than starting number

I am trying to find a formula that will automatically add the sum.

For example let's say I start with 10. I want to start one number below 10 & add them all up. For example, if starting number is 10 then I would take: 9+8+7+6+5+4+3+2+1. I need to be able to do this for any starting number.

If I start at 100 it would be 99+98+97+96+96+94 all the way down to zero. If starting at 56, I would need to start adding 55+54+53+52+51+50 all the way to zero.

Do you know a formula I can use? The starting number can be any number, but I need to start one number below the starting numbers and add each following number.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

LANB5669,

There isn't (that I'm aware of) a specific function or formula that will do what you are asking for. However, Excel is a very flexible, powerful tool, and with a little imagination/creativity, by combining a few functions it can handle a huge range of things.

So in plain English, you want to be able to input a number, and have Excel subtract 1 from that number, then subtract one from each resulting number until you reach zero, then add those numbers.

The attached spreadsheet asks the user for a numerical input in A2. B2 then subtracts 1 from the number in A2, provided A2 is greater than zero, with:

=IF(A2="","",IF(A2>0,A2-1,""))

B3 subtracts 1 from B2, provided B2 is greater than 0..... and so on, all the way to the bottom of the sheet. (If your number is bigger than 1048575, you'll need a little workaround, which just involves the help of an extra column.)

C2 simply SUMS the values in Column B.


EDIT: I have deleted the formula after B300, as having it run to the end of the sheet meant the file was too big to upload. If need be, copy the formula down yourself

an alternate method, assuming your number is in A2, use...=A2*(A2-1)/2. using this formula, you wont need a helper column, it will all be done in a single cell, for any value you choose

"For people who know their maths"! That's lovely, FDibbins - thanks for sharing.

you'r welcome, and thx for the star