DoctorData
New Member
- Joined
- Jan 29, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
mode calcs.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
2 | Size (mm) | Amount (%) | Peak above | Peak above | Threshold A (above base) | 2 | |||
3 | Threshold A? | Threshold B? | Threshold B (above adjacent troughs) | 2 | |||||
4 | 63 | 0.00 | |||||||
5 | 45 | 14.22 | 45 | 45 | |||||
6 | 31.5 | 4.99 | FALSE | FALSE | First peak (above Threshold A) | 45 | |||
7 | 22.4 | 1.24 | FALSE | FALSE | Second highest peak (above Threshold A) | 11.2 | |||
8 | 16 | 13.20 | FALSE | FALSE | Third highest peak (above Threshold A) | 4 | |||
9 | 11.2 | 13.80 | 11.2 | FALSE | |||||
10 | 8 | 13.51 | FALSE | FALSE | First peak (above Threshold B) | 45 | |||
11 | 5.6 | 13.31 | FALSE | FALSE | Second highest peak (above Threshold B) | 4 | |||
12 | 4 | 15.89 | 4 | 4 | Third highest peak (above Threshold B) | #NUM! | |||
13 | 2.8 | 12.12 | FALSE | FALSE | |||||
14 | 2 | 6.69 | FALSE | FALSE | |||||
15 | 1.4 | 3.30 | FALSE | FALSE | |||||
16 | 1 | 1.03 | |||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C5:C15 | C5 | =IF(AND(B5>=B4,B5>B6,B5>=G$2),A5) |
D5:D15 | D5 | =IF(AND(B5>=B4,B5>B6,B5>=G$2,(B5-B6)>G$3,(B5-B4)>G$3),A5) |
G6 | G6 | =LARGE(C5:C15,1) |
G7 | G7 | =LARGE(C5:C15,2) |
G8 | G8 | =LARGE(C5:C15,3) |
G10 | G10 | =LARGE(D5:D15,1) |
G11 | G11 | =LARGE(D5:D15,2) |
G12 | G12 | =LARGE(D5:D15,3) |
I have sets of data where I want to find peaks (or 'modes'). These are actually amounts of stones in different sizes. The spreadsheet simply finds values where the values either side are lower. But I have two situations where I don't want peaks to be identified:
(1) Threshold A where peaks are not large. In the spreadsheet I have set an additional condition where the peak must be larger than 2% (but in this example all three peaks are larger than 2%)
(2) Threshold B where peaks are larger than adjacent troughs but a set amount. In the example I have set this to 2%. HOWEVER in the spreadsheet it only finds troughs one row above or below each value. I need to be able to identify a trough any distance above or below. In the example of Peak 2 the troughs are 2 rows up and 2 rows down. Is there a way to use INDEX and MATCH to search up and down from each point?