CODEWITHSUNDEEP
exceldatabaseexcel-formulaadditionspreadsheet
the sigmoid infinity
the sigmoid infinity

Reputation: 123

Summation of function inputs in excel

I have been playing with the Fourier Series, to be precise, Fast Fourier Transform.

These are the parametric coordinates for my to-be-made graph. (I'll explain this shortly)

Latex please :((

The x,y,z have list inputs as shown below:

Pickachuuuuuu

Here, t varies from 0 to 1

Basically what the x[1] does is pick the first element from the table (This is a desmos table and item selection protocol)

What I want to do: I want to make an excel sheet do the same thing. In short, I want to find the summation from (n=1) upto (n=1000) of function a*f(b*t+c) [where a, b and c are variables given as x,y,z in the spreadsheet and f is the sine or cosine function] at evenly spaced out time intervals in a single cells using the least possible number of formulas.

What I have done so far and problems faced:
Created an excel sheet with inputs of x,y,z ; I am stuck at how to implement the summation without a lot of hassle, I know how to make t progress with an interval as small as 0.00...01 and put it on the sheet but I am not able to implement the summation so that I directly get the (x,y) in table form. What I thought was to do it manually for dt=0.1, i.e. keeping t fixed but x,y,z progressing and then find a summation of all the outputs, but in this way the (x,y) are very imprecise, and obviously doing it manually for t=0.00001 would be not so intelligent if one has a work around.

A sample dataset for clarity: Sample

Here, I wanted to have Row 17 directly.

Upvotes: 0

Views: 217

Answers (1)

jblood94
jblood94

Reputation: 16981

Here are the steps I took with dt = 0.001 (1001 parametric coordinates):

  1. Put the x1, y1, z1 data into A2:C1001
  2. Put the values of t into column E starting with E2
  3. In Formulas > Name Manager, create the following variables: x = 2*PI()*TRANSPOSE($A$2:$A$1001); y = TRANSPOSE($B$2:$B$1001); z = PI()*TRANSPOSE($C$2:$C$1001)/180
  4. Column F will hold the first dimension of the parametric coordinates. Select F2:F1002 and enter this CSE formula (commit with Ctrl + Shift + Enter): =MMULT(y*cos(x*E2:E1002+z),--(ROW(A2:A1001)>0))
  5. Column G will hold the second dimension of the parametric coordinates. Select G2:G1002 and enter this CSE formula: =MMULT(y*sin(x*E2:E1002+z),--(ROW(A2:A1001)>0))

UPDATE: Simpler formulas for steps 4 and 5 that don't require CSE (also, this wouldn't require TRANSPOSE in the named range definitions):

  1. In F2: =SUMPRODUCT(y,COS(x*$E2+z)), then fill down
  2. In G2: =SUMPRODUCT(y,SIN(x*$E2+z)), then fill down

Upvotes: 1

Related Questions