If there's an Excel god, please read this post.

So, I decide to do a project in Excel instead of Matlab.

It was implied that Matlab would be the program of choice for this project but it was not required (in my defense). 

Anyways, now I have three sets of working Matlab code for this project that I could use. But it's become personal.

Here's the math (this is for a method of tracking typical oceanic wave heights for a given frequency or time, FYI. Google JONSWAP if you want to learn the specifics): 

eta(t=0) = (A0*cos(w0*t0+e0)) + (A1*cos(w1*t0+e1))+.....(An*cos(wn*t0+en)

aka: 

This repeats for eta (t = 1-1000)

I tried this : =sum($H$8:$H$1032*cos($D$8:$D$1032*$I8+$B$8:$B$1032)), where the I column is all my times, 1-1000, and it doesn't work... unless there's a bunch of half-meter tall waves in an ocean where the significant wave height is 16 meters (there aren't).

Is there a way to express this in Excel such that I don't have a bunch of (literally about a thousand) different columns for each time dependent summation? Ideally, I'd like one column, 1-1000, that covers all summations for all times.  

What is in columns H, D, and B? I'm assuming that A0, w0, t0, e0, etc., are all a specific number and are not variables?

You're asking Excel to do the wrong thing - you need it to take 1,000 different cosines; you're just having it take one. This is how I would do it: 

A, w, t, and e all get their own columns. Each column gets an Index 1-1000. So I would make Column A be "Index" representing n, Column B be "A", Column C be "w", Column D be "t", and column E be "e"

Fill them in - whatever each An, wn, tn, and en are in the respective spot. 

Next, make a Lookup Cell. THis will be your instance lookup value. Put it in B1. 

Below that, in B2, have an outcome cell. This will be sum(F4:F1000)

Now go into F4. Type in your formula. It will be this: =B4*cos((C4*vlookup(B$1,A:D,4,false)+E4))

Drag that down to the end of your index, and it should get you the formula you posted a picture of for n(t) - each index in column f representing one equation to be summed.

Quick and dirty example in an old Google Doc I have for something else, since I'm not near my work computer with working Excel - and the MPG calculator on this is somehow broken, but I haven't looked at it in years: 

https://docs.google.com/spreadsheets/d/1fHro6pDvZYK2lyeV9ingrdbzEkXNfhqcJU65cVggGPo/edit#gid=2029692260

Make sure you're on the right worksheet. The "attempt to fix formula" one. You should be able to edit it. Assuming that you can plug in A1-1000, w1-1000, t1-1000, and e1-1000, it should be about what you're looking for.

Ultimately this is something I would do in Excel only because I'm overly comfortable with it. Definitely something I would use MATLAB for if it were available to me.

In reply to Turbo_Rev :

I think what you want is an array formula.  Can you share an example file or dataset?

H,D, and B columns are indeed those variables. They all have about 1024 values, from row 8-1035. So, A1, A2, A3.......A1024. I'll try what you mentioned.

https://docs.google.com/spreadsheets/d/1aTCItg39xS5xi2H-3wsWlFzsnrIWe2bZGQQvgLrlj_g/edit?usp=sharing

That should allow viewing. 

ProDarwin said:

In reply to Turbo_Rev :

I think what you want is an array formula.  Can you share an example file or dataset?

 

Ah, you've got it. My brain doesn't work at 2AM apparently. 

For the same dataset I used: =SUM($B$4:$B$1003*COS($C$4:$C$1003*VLOOKUP($B$1,$A$4:$D$1003,4,FALSE)+$E$4:$E$1003))

Then, hit CTRL+Shift+Enter. It should work if you're using a recent enough version of Excel.

Note here, Column B contains A, column C contains w, column D contains t, column E contains e.

=ArrayFormula(SUM($H$8:$H$1032*cos($D$8:$D$1032*$I8+$B$8:$B$1032))) was the winner.

That's the free surface condition of the ocean in the Gulf of Mexico, in case you were wondering. 

I think what I learned here is never underestimate Excel but use MatLab, if you have it.

Anyways, you have assisted a fightin' Texas Aggie and, as a result, Mrs. Rev smiles upon you this day:

For something quick like this I would almost always lean toward excel.

If you are going to process lots of datasets like this, matlab makes more sense.  When you have a bunch of array formulas and large datasets excel starts to become unwieldy.

ProDarwin said:

For something quick like this I would almost always lean toward excel.

If you are going to process lots of datasets like this, matlab makes more sense.  When you have a bunch of array formulas and large datasets excel starts to become unwieldy.

I've crashed Excel too many times trying array formulas that I'd move on to something else... Some of that is because I always have about 50 different spreadsheets open, but lets not get bogged down with how messy my virtual desktop is and how that may hinder computer performance...

Turbo_Rev said:

I think what I learned here is never underestimate Excel but use MatLab, if you have it.

As a MathWorks staff member, I approve this message... 

mtn said:

ProDarwin said:

For something quick like this I would almost always lean toward excel.

If you are going to process lots of datasets like this, matlab makes more sense.  When you have a bunch of array formulas and large datasets excel starts to become unwieldy.

I've crashed Excel too many times trying array formulas that I'd move on to something else... 

I have done the same.  But this one is relatively simple.

I also find that writing the import script for whatever data I am bringing in to Matlab is hugely time consuming if its a data type I haven't used before, so I tend to lean toward excel for a first pass.  Again, for small data sets.

Also, I don't live in Matlab day to day, I only use it once every few months so it always involves re-learning a lot of the array/matrix syntax :(

This thing was N = 1000, which is just getting on the edge of un-wieldy for my tastes in Excel.

BUT doing an FFT is a lot easier in Excel, so it's looking like it might have ended up being a wash.