Given a value (in a cell), calculate a formula like this:
Ʃ(3i+1) for i from 0 to the value specified in the cell.
SUM(), SERIESSUM() are not suitable in this case. How can I do this in Excel? Much thanks!
6 Answers
To leave you all options, just use this:
=SUMPRODUCT(3*(ROW(A1:INDEX(A:A,B1-A1+1))-(1-A1))+1)
A1 is the lower limit and B1 is the upper limit... will also work for ranges like i = -5 to -3 ;)
Demo as below graph:
Write in B2 Cell with =SUM(3*(ROW(INDIRECT(CONCATENATE("1:",A2+1)))-1)+1) then press Ctrl + Shift + Enter.
Use the array formula:
=SUM(3*ROW(INDIRECT("1:" & A1))+1)+1
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
You don’t need complexe functions, Just calculate (max-min+1)*((formula min)+(formula max))/2
In other words range times the average of formula min/max
Here is an example using Mac numbers for a range min=1/max=5
For the average you can use the function AVG but this is the no functions solution.
[To complement Dirk Reichel's answer (I added a $ at "ROW(A$1)":
"=SUMPRODUCT(3*(ROW(A$1:INDEX(A:A,B1-A1+1))-(1-A1))+1)"
Here is what it looks like. (see the image)
No indirect, no array, and can be cut past anywhere. (Needed the $ sign to be cut and past anywhere)]
What about
=(3/2*n+1)*(n+1)
for 0 as lower bound and n is your upper bound
