I have a spreadsheet that MWE to:
| a | b | c |
|---|---|---|
| 1 | 3 | 5 |
| 2 | 4 | 6 |
I would like to have a new column, where I calculate the sum of all a_n*exp(-(x-b_n)^2/c_n), where x would be a parameter given in another column and n is the row.
I would think this would be something the likes of=SUM(A2:A3*EXP(-($D3-B2:B3)^2/C2:C3)) but this doesn't work (here the D column holds the x values)
It is called spreadsheet calculation. So we should using the sheet having multiple cells. If we need sum of all values, we do calculating the single values first and then we sum them using SUM function.
Example:
Formulas:
D5 = =$A5*EXP(-(D$3-$B5)^2/$C5) copied down and to right to F12.
Because the row references in $A5, $B5 and $C5 are relative (not fixated using $) those row references will be updated while copying downwards. And because the column reference in D$3 also is relative this column reference will be updated while copying sidewards.
D13 = =SUM(D5:D12) copied to right to F13.
All in one is only possible using formulas in array context. The function SUMPRODUCT takes all of its parameters in array context.
Formula in D15 = =SUMPRODUCT($A$5:$A$12*EXP(-(D$3-$B$5:$B$12)^2/$C$5:$C$12))
Another possibility would be using array context in SUM function:
Formula =SUM($A$5:$A$12*EXP(-(D$3-$B$5:$B$12)^2/$C$5:$C$12)) - results in #VALUE!. Then click fx and tick check-box [x] Array:
