average amplitude

Hi,

I would like to know how to find the average amplitude for heave and pitch motions by using "if" function.

If you are asking for the average of heave when x (Col A) is (for example) between 6 and 8.

=SUMIFS(F23:F822,A23:A822, ">=6", A23:A822,"<=8")/COUNTIFS(A23:A822, ">=6", A23:A822,"<=8")

something like this.

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

It is not clear to me what you are looking for or exactly what you need help with. It is clear to me that this kind of "signal processing" is almost certainly more than just a simple IF() function. Perhaps a few questions to promote some discussion and reflection and research:

1) How would you do this by hand? I often find that part of programming a problem is to first understand how I would work the problem by hand, then I can work on "translating" those steps and procedures into my desired programming language.
1a) If you already have an established procedure, describe that procedure to us and we should be able to help you program that in Excel. Better yet, Specifically identify the steps that you can do and those you can't so our responses and assistance can focus on the parts you need help with without the need to address the issues you already know how to do.
1b) If you do not yet have an established procedure, some research into signal processing algorithms, and how they are used to measure amplitude (and frequency and other such parameters) could be useful. I suspect there are a few different algorithms and procedures, with advantages and disadvantages for each. I would not be surprised if this is the kind of question you might face when you defend the dissertation -- why did you choose this algorithm for processing peaks/troughs over other algorithms.
2) How are you defining amplitude? A standard trig function (y=Asin(B) for example), A is the amplitude and determines/measures how far above "baseline 0" the function's peaks occur. I could also see defining amplitude as the distance between trough and peak.
3) Your question in post #5 asks about finding peaks and troughs. How do you normally find peaks and troughs (or max/min)? I tend to use what I learned in calculus (max/min occur where the function's slope/1st derivative is 0), which usually means a column to compute slope/derivative, which can then serve as a basis for locating where the max/min peaks/troughs occur.
4) I have no experience with Fourier transforms, but it seems possible that a Fourier regression might have a parameter (or combination of parameters) that could be interpreted as "average amplitude". If your research for part 1 suggests that Fourier transforms/regressions are applicable here, then you can research how to implement this analysis in Excel (such as this example by user shg: https://www.excelforum.com/excel-cha...dal-curve.html ).

Those are my thoughts on the problem. Do they help at all?

Not sure that this is what you want but it divides the maximum displacement by 2:

Formula:

Please Login or Register  to view this content.

Thanks for the rep! Did that help?

just now updated my excel file and you can see at the top.

Sample file got lost in the edit?

With the understanding that we are looking for the "distance between trough and peak" (Post #9), or the distance between the local minima and its corresponding local maxima, as Mr. Shorty stated earlier, this proposed method uses several helper columns with relatively simple formulas.
To find prospective local maximas for heave (column K): =IF(AND(F23>=F22,F23>F24),F23,"")
To find prospective local minimas for heave (L): =IF(AND(F23<F22,F23<=F24),F23,"")
To find corresponding prospective local maiximas and minimas (M and N): =IFERROR(INDEX(K$23:K$822,AGGREGATE(15,6,(ROW(K$23:K$822)-22)/(K$23:K$822<>""),ROW(A1))),"") =IFERROR(INDEX(L$23:L$822,AGGREGATE(15,6,(ROW(L$23:L$822)-22)/(L$23:L$822<>""),ROW(B1))),"")
To find the height of each prospective wave (O): =IFERROR(M23-N23,"")
To find the average ignoring plateaus (P23): =AVERAGEIFS(O23:O140,O23:O140,">"&0)
The formulas for Pitch are similar.
Let us know if you have any questions.

Originally Posted by JeteMc

With the understanding that we are looking for the "distance between trough and peak" (Post #9), or the distance between the local minima and its corresponding local maxima, as Mr. Shorty stated earlier, this proposed method uses several helper columns with relatively simple formulas.
To find prospective local maximas for heave (column K): =IF(AND(F23>=F22,F23>F24),F23,"")
To find prospective local minimas for heave (L): =IF(AND(F23<F22,F23<=F24),F23,"")
To find corresponding prospective local maiximas and minimas (M and N): =IFERROR(INDEX(K$23:K$822,AGGREGATE(15,6,(ROW(K$23:K$822)-22)/(K$23:K$822<>""),ROW(A1))),"") =IFERROR(INDEX(L$23:L$822,AGGREGATE(15,6,(ROW(L$23:L$822)-22)/(L$23:L$822<>""),ROW(B1))),"")
To find the height of each prospective wave (O): =IFERROR(M23-N23,"")
To find the average ignoring plateaus (P23): =AVERAGEIFS(O23:O140,O23:O140,">"&0)
The formulas for Pitch are similar.
Let us know if you have any questions.

Hi JeteMc,

I am not sure why you used Aggregate(15,6,...) for both maxima and minima. 15 is for small, right? Is it 14 for large(maximum)? I also do not get the term "(ROW(K$23:K$822)-22)/(K$23:K$822<>"") and Row(A1)used in index function. Could you explain these please? Also how can I find the equation that you showed on the curve? That is great , cool.

Regards
May

Yes, 15 is for Small and 14 for Large however the formula is looking for the rows with values in columns K (and L), not for local maxima and minima in column F.
...(ROW(K$23:K$822)-22)/(K$23:K$822<>"") is the part of the equation that sets up an array of row numbers that have values and reports #Div/0 for those that do not (which the 6 causes the AGGREGATE function to ignore).
...ROW(A1) returns the number 1 so that, for example, cell M23 will display the first maxima is in the 5th row of the range K23:K822, cell M24, ROW(A2), displays the second maxima is in the 17th row etc.
Notice that N23 and N24 display the first and second minimas.
One way to see what the formula is doing is to use the Evaluate Formula feature found on the Formulas tab. I would suggest selecting in turn cells M23, N23, M24 and N24 to get a sense of what is going on.
As far as the equation on the graph, it is an option of the format trendline dialog box which will come up if you right click the trendline (black) on either chart. Toward the bottom of the dialog box you'll notice that 'Display equation on chart' has been selected.
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.