The formula I would like to use looks something like this: SUMPRODUCT(x^(1:n),y^(n:1)). n=values in column A. 1:n is the exponents in forward progression from 1 to n in steps of 1. n:1 is the exponents in reverse progression from n to 1 in steps of 1. I would like the formula to be dynamic to fill in column B with the n values based on column A.
5 Answers
Try:
=SUMPRODUCT(5^ROW(1:100))
Or in Excel O365
=SUM(5^ROW(1:100))
As per @RonRosenfeld, a more sturdy solution could be =SUM(5^SEQUENCE(100)) in Excel 365.
EDIT: Based on OP's comments he could use (no O365):
=SUMPRODUCT(5^ROW(A1:INDEX(A:A,COUNTA(A:A))),7^LARGE(ROW(A1:INDEX(A:A,COUNTA(A:A))),ROW(A1:INDEX(A:A,COUNTA(A:A)))))
You can store the powers in a column and use the array formula:
SUM((A1:A100)^$B$1) where A column contains 5 in each cell and B column contains the range of powers you want to use. You can use an array formula in the different cell to get the answer.
Use the SERIESSUM function
The Excel SERIESSUM function returns the sum of a power series, based on the following power series expansion:
Power Series Equation
The syntax of the function is:
SERIESSUM( x, n, m, coefficients )
Where the function arguments are:
x - The input value to the power series.
n - The first power to which x is to be raised.
m - The step size that n is increased by, on each successive power of x.
coefficients - An array of coefficients that multiply each successive power of x.
The number of values in the supplied coefficients array defines the number of terms in the power series. This is illustrated in the following examples.
Example 1:
In the spreadsheet below, the Excel Seriessum function is used to calculate the power series:
5^1 + 5^2 + 5^3 + 5^4 + 5^5
formula: =SERIESSUM( 5, 1, 1, {1,1,1,1,1} )
output = 3905
Example 2:
1 * 2^1 + 2 * 2^3 + 3 * 2^5 + 4 * 2^7 + 5 * 2^9
formula: =SERIESSUM( 2, 1, 2, {1,2,3,4,5} )
output = 3186
I hope this is of help.
An Alternative Answer again. I think the correct for your case :-)
Using the SERIESSUM function allows the use of different coefficients therefore the reason for the use of the coefficients in an array. But because the coefficients are the same then this is simply a geometric progression.
The following formula will do that for you:
=n+n*(n)^(1)*(1-(n)^c)/(1-n)
where "n" is the number (5) and "c" is the number of the series (100)
This becomes:
=5+5*(5)^(1)*(1-(5)^100)/(1-5)
=SUMPRODUCT(5^ROW(A1:INDEX(A:A,COUNTA(A:A))),7^LARGE(ROW(A1:INDEX(A:A,COUNTA(A:A))),ROW(A1:INDEX(A:A,COUNTA(A:A)))))
This formula worked flawlessly!!!
Thank you @JvdV and everyone else for your efforts in helping me! GREATLY APPRECIATED!
Related