Excel includes the FACT worksheet function which returns the factorial of a value. (The factorial of the number X is the result of multiplying 1 * 2 * 3 … * X.) Sabeesh wonders if there is a similar function that will return the sum of the values (1 + 2 + 3 … + X) instead of the result of the values.
There is no such function built into Excel, but a quick mathematical formula will do the trick. The proper terminology to refer to this type of sum is a “triangular number.” This derives from the fact that if the sum was represented with objects, they could always be arranged in the form of a triangle. For example, if you had 5 objects on the bottom row, 4 on the next, 3 three on the third, 2 on the fourth, and 1 on the top row, you have a triangle. Summing the number of objects (5 + 4 + 3 + 2 + 1) is what Sabeesh wants to do.
The answer to this problem can be expressed as a mathematical formula, reportedly discovered by Carl Friedrich Gauss. (Which is the source for another name of this type of number: a Gaussian Summation.) Note that the sum of opposite rows in the above example are always the same: 5 + 1 is the same as 4 + 2. This is true regardless of the number of rows; if there were 100 rows, then 100 +1 is the same result as 99 + 2, 98 + 3, 97 + 4, etc. What you end up with is 50 “pairs” of numbers equal to 1 more than the upper limit of your range.
The upshot of all this-without going through a lot of explanation-is that you can find the triangular number for any positive value (where you start at 1 and end with X) in the following manner:
Thus, if you had a number in cell A1 and you wanted to know the sum of the range of 1 through that number, you could use this formula:
This formula provides a simple way to determine the sum required, without the necessity of resorting to using a macro.