Finding a peak value in an array within an array

[Burkie]

Hi. Need help please.

• I have a set of 100,000 CO2 readings, taken every 15 minutes from classrooms in a school.
• I am interested in the reading once it goes over 650 ( identified by the green in the attached extract )
• I need to identify the PEAK value WHILE it is WITHIN the 650 zone.
• There are a number of sub-peaks within the zone ( 5 in the extract I attached )
• So, I need a formula that knows its within the 650 zone and identifies the peak of the peaks and then resets when it leaves the 650 zone !

THANK YOU.

Burkie.

 

[Burkie]

Thanks Guys. Really appreciate the help.
I'm attaching a sample of the bigger file. Each point where the value go over 650 and enter the zone, is different i.e, sometimes it might only be in the zone for
5 points, other times it could be 20. In fact, sometimes the ENTRY data point is actually the peak value as well ! So looking back and forward a fixed number of cells cannot be used.
Essentially, the solution needs to mark the entry point and start measuring each value until it gets to the exit point, then it needs to mark the highest value it encountered.
Maybe a VBA is the only way to do it. I've never used VBAs before ( just youtubing it now ).
PS: I not allowed download the XL2BB tool.

 

[felixstraube]

Waiting for the sample data.
A graph of what you have and what you need would be helpful too. Explaining in detail all the cases you mentioned and explain exactly what you expect. It is not very clear what you need exactly.

 

[Burkie]

Sorry. I couldnt attach the sample.

 

[Burkie]

Sorry. I couldnt attach the sample.

I will try to explain better what the situation is. Sorry for not being clear.

 

[Burkie]

Hopefully. This might help.

 

[felixstraube]

Maybe this will do?

I uploaded a working file here: FindingPeaks.zip

You can test differente (randomly generated) sample data by pressing F9.

Let me know if this works for you. And we can clean the file up and make it a one cell formula.

 

[felixstraube]

Here is the one cell formula:

FindingPeaks2.zip

As before you can test with generated sample data by pressing F9.

The formula used in B2:

FindingPeaks2.xlsx
	A	B
1	Data	Result
2	738
3	766	766
4	670
5	734
6	602
7	658
8	659
9	728	728
10	664
11	642
12	577
13	516
14	638
15	765
16	826
17	693
18	727
19	805
20	715
21	742
22	806
23	779
24	820
25	879	879
26	780
27	791
28	687
29	736
30	758
31	700
32	776
33	676
34	765
35	786
36	722
37	747
38	719
39	771
40	692
41	597
42	580
43	542
44	457
45	584
46	511
47	573
48	577
49	519
50	438
51	482
52	521
53	521
54	617
55	711
56	776
57	833
58	885	885
59	811
60	832
61	833
62	757
63	663
64	645
65	558
66	431
67	429
68	432
69	555
70	652
71	719
72	772
73	845	845
74	756
75	660
76	596
77	457
78	545
79	457
80	503
81	425
82	547
83	695
84	684
85	664
86	802
87	844
88	856	856
One cell solution

Cell Formulas
Range	Formula
B2:B88	B2	=LET( d,A2:A88, above, d>650, prevAbove, DROP(VSTACK(0,above),-1), entryPoint, above>prevAbove, id, DROP(REDUCE({0},entryPoint,LAMBDA(x,y,VSTACK(x,MAX(x)+y*1))),1), dataId, HSTACK(d,id), BYROW(dataId, LAMBDA(x, IFERROR(IF(CHOOSECOLS(x,1)= MAX(FILTER(d, (id = CHOOSECOLS(x, 2))*(above))), CHOOSECOLS(x,1),""),"") )) )
A2	A2	=550+RANDBETWEEN(0,200)
A3	A3	=A2+RANDBETWEEN(-100-(AVERAGE(A1:A2)>650)*50,100+(AVERAGE(A1:A2)<650)*50)
A4	A4	=A3+RANDBETWEEN(-100-(AVERAGE(A1:A3)>650)*50,100+(AVERAGE(A1:A3)<650)*50)
A5	A5	=A4+RANDBETWEEN(-100-(AVERAGE(A1:A4)>650)*50,100+(AVERAGE(A1:A4)<650)*50)
A6:A88	A6	=A5+RANDBETWEEN(-100-(AVERAGE(A1:A5)>650)*50,100+(AVERAGE(A1:A5)<650)*50)
Dynamic array formulas.

Let me know if this works for you.