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

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.

enter image description here

1

Please try:

=x*SERIESSUM(.95,1,1,{1,1,1,1,1})

where the array has n 1s (5 for example).

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy