Summation Formula Needed
I Need Help with Creating a Formula in Google Sheets for the Sum of: X*(0.95^1+0.95^2+0.95^3+. .. +0.95^N) Where X Is a Constant, and N Is a Positive Integer...
I need help with creating a formula in Google Sheets for the sum of:
x*(0.95^1+0.95^2+0.95^3+...+0.95^n)
where x is a constant, and n is a positive integer. The formula must fit in one cell.
2 Answers
=x*SERIESSUM(.95,1,1,{1,1,1,1,1})
While there very possibly a ready-made statistical or financial worksheet function for this, you seem to be happy with the results from this formula offered in another answer.
You just need to generate the array n times with n being the value from a cell on the worksheet.
INDEX(ROW(1:4), , ) returns a result of {1, 2, 3, 4} as a true array.
To convert {1, 2, 3, 4} to {1, 1, 1, 1} use a SIGN function 'wrapper' around the row numbers.
=INDEX(SIGN(ROW(1:4)), , ) 'returns {1, 1, 1, 1} as a true array
To vary the length of the array, use INDIRECT to build the ROW reference. With 4 in C10, this returns the same result.
=INDEX(SIGN(ROW(INDIRECT("1:"&C10))),,) 'returns {1, 1, 1, 1} as a true array
'proof
=SUM(INDEX(SIGN(ROW(INDIRECT("1:"&C10))),,)) 'returns 4
Put that together with the rest of the formula as a standard formula with INDEX in array mode.
=B10*SERIESSUM(0.95, 1, 1, INDEX(SIGN(ROW(INDIRECT("1:"&C10))),,))
If you don't wish to use INDEX in its array mode, you can dispense with it and add an ARRAYFORMULA wrapper instead.
Please try:
=x*SERIESSUM(.95,1,1,{1,1,1,1,1})
where the array has n 1s (5 for example).
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
