Thread: Multiple Max Peak Search (Excel 2002/10.2614.2625)

1. Multiple Max Peak Search (Excel 2002/10.2614.2625)

Here's a brain teaser for the EXCEL experts out there. I can't include the spreadsheet. It's 186kb. Please email me at kmouser (nospam@nospam) ameritech.net if you would like to try your hand at it. You all are probably very familiar with this but I'm going to say it anyway: Remove the (nospam@nospam) and replace with the @ sign in the email address.
I'm using a degrading sine wave for data input. It's the sine wave from an object that is being struck - similar to a tuning fork - to measure its vibration properties. As the vibration dissipates, the peak-to-peak value decreases. The spreadsheet file contains typical data that must frequently be analyzed. What I need from this data is the maximum positive response value and the elapsed time at the first 10 maximum positive peaks. The table below is what I'm looking for. I got this data by fiddling around with the "vlookup" function and got it to automatically determine the first largest peak, but the next nine peaks were found manually, or by manual manipulation of the "vlookup" command. The spreadsheet shows my formulas for manually finding the data.

Peak No.// Response (g)// Time (msec)//
1) 0.758049 // 22.99
2) 0.6672071 // 38.39
3) 0.5743081 // 53.9001
4) 0.461133 // 69.0801
5) 0.4006204 // 83.0501
6) 0.3844773 // 98.2301
7) 0.3215181 // 112.42
9) 0.2944977 // 142.45
10) 0.2215092 // 156.31
11) 0.2123356 // 170.61
12) 0.2120002 // 185.57
13) 0.1746133 // 199.1
15) 0.1691097 // 228.25
18) 0.1483855 // 272.36

Any suggestions on how to automate the determination of the first 10 peaks will be appreciated.
Thank you VERY much in advance for anyone who can figure this out or who already knows the way! I will be forever in your debt!
Kim Mouser

2. Re: Multiple Max Peak Search (Excel 2002/10.2614.2625)

Hi kmouser,

Try the attached spreadsheet. In it I've augmented and 'randomised' your data in columns A-C. Then, in Column E I've specified a required ranking of 1-10. Column G uses the "LARGE" formula to find the peak value in Column B that matches the ranking in Column E. Columns F & H both use "INDEX" & "MATCH" to find the peak # & ET values in Column B that correspond with the values in Column F.

You'll notice that there's two ranking tables, using different formulae. The difference is that the upper set won't correctly report the peak # or ET for duplicated peak values in Column B, but the more convloluted lower set treats them correctly. The second one achieves this by testing for a duplicate peak value with the row above and, if found, uses an "OFFSET" parameter to find the next peak # & ET values.

3. Re: Multiple Max Peak Search (Excel 2002/10.2614.2625)

Thank you macropod! That worked!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•