Finding a peak value in an array within an array

B

Burkie

New Member
Joined
Jun 4, 2024
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • Finding the PEAK within an Array.PNG
    Finding the PEAK within an Array.PNG
    82.9 KB · Views: 22

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Why is 915 a peak when the next entry is 920?

It looks like you just need to check if the cell value is higher than the one above and below it, and that it is also over 650.
 
Upvote 0
Sorry. my mistake ( my head is frazzled from trying to find a formula for this ! ). It should be the 920.

I dont think checking one cell back and one c3ell forward will sort it as the previous ' peak ' might be 4 cells back.
 
Upvote 0
Burkie said:
I need to identify the PEAK value WHILE it is WITHIN the 650 zone.
Click to expand...
You've said you want the peaks when the CO2 (ppm?) is over 650, so do you mean within the OVER 650 zone?

I can't tell your columns from your graphic, but let's say your temp readings are in column H, why wouldn't a simple formula like this work?

Excel Formula: 
=IF(AND(H5>H4,H5>H6,H5>650),H5,"")
 
Upvote 0
One other thing, when you post an image of your data instead of something that can be copied and pasted into a spreadsheet, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool (link below) to post your data in a way that makes it accessible to others.

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com
 
Upvote 0
Thanks for the replies. I will extract some of the data into a mini file and add it here soon.
I think its not just about looking one cell behind and one cell in front. It needs to remember the peak from 4 or 7 or 8 cells back and compare it.
( Yes, it needs to know its within the OVER 650 zone. Once it goes out of that mini zone, it needs to reset until it goes into the next OVER 650 Zone ).
THANKS.
 
Upvote 0
Burkie said:
Sorry. my mistake ( my head is frazzled from trying to find a formula for this ! ). It should be the 920.

I dont think checking one cell back and one c3ell forward will sort it as the previous ' peak ' might be 4 cells back.
Click to expand...
Ah, I thought you were trying to calculate those intermediate peak values.
 
Upvote 0
I think you'll want to use something like a LOOKUP to find the last cell above the current one that is <650 and a MATCH to find the first one below that is less than 650, then get the MAX value in between. Easier if we can see where the actual data is.
 
Upvote 0
Burkie said:
Thanks for the replies. I will extract some of the data into a mini file and add it here soon.
Click to expand...
If the sample data file shows a mockup for the desired identified peaks - something that can be used to test any proposed formula, that would be helpful. You seem to be looking for a formula based solution, but something to keep in mind is that a VBA solution could be an alternative if the formula approach does not work out.
 
Upvote 0
Maybe something like this?
Checking if you are on the max value 3 cell back and 3 cell forward.
It would need some error handling for the first 3 rows. If this is what you need we could look into it.

Libro1
ABCDEFGHIJ
1Class 1108/01/20241Monday08:17Y519N#¡REF!
2Class 1108/01/20241Monday08:31Y515N#¡REF!
3Class 1108/01/20241Monday08:48Y556N#¡REF!
4Class 1108/01/20241Monday09:02Y808Y 
5Class 1108/01/20241Monday09:17Y915Y915 
6Class 1108/01/20241Monday09:32Y920Y 
7Class 1108/01/20241Monday09:47Y884Y 
8Class 1108/01/20241Monday10:02Y912Y 
9Class 1108/01/20241Monday10:17Y945Y945945
10Class 1108/01/20241Monday10:32Y903Y 
11Class 1108/01/20241Monday10:47Y900Y 
12Class 1108/01/20241Monday11:02Y905Y 
13Class 1108/01/20241Monday11:17Y909Y909 
14Class 1108/01/20241Monday11:32Y899Y 
15Class 1108/01/20241Monday11:47Y924Y 
16Class 1108/01/20241Monday12:01Y979Y 
17Class 1108/01/20241Monday12:16Y1006Y1,0061006
18Class 1108/01/20241Monday12:31Y979Y 
19Class 1108/01/20241Monday12:46Y890Y 
20Class 1108/01/20241Monday13:01Y815Y 
21Class 1108/01/20241Monday13:16Y852Y 
22Class 1108/01/20241Monday13:46Y912Y912912
23Class 1108/01/20241Monday14:01Y844Y 
24Class 1108/01/20241Monday14:16Y853Y 
25Class 1108/01/20241Monday14:31Y908Y908 
26Class 1108/01/20241Monday14:45Y839Y 
27Class 1108/01/20241Monday15:OOY786Y 
28Class 1108/01/20241Monday15:15Y742Y 
29Class 1108/01/20241Monday15:30Y703Y 
30Class 1108/01/20241Monday15:46Y681Y 
31Class 1108/01/20241Monday16:01Y665Y 
32Class 1108/01/20241Monday16:16Y650Y 
33Class 1108/01/20241Monday16:31Y640N 
34Class 1108/01/20241Monday16:46Y626N 
35Class 1108/01/20241Monday17:01Y624N 
Hoja1
Cell Formulas
RangeFormula
J1:J3J1=IF((G1>650)*(G1=MAX(#REF!)),G1,"")
J4:J35J4=IF((G4>650)*(G4=MAX(G1:G7)),G4,"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Determine match based on if a date range falls within a larger date range :S
Replies
1
Views
323
dermie_72
D
L
  • Question
Find value of cell within a range and replace with value in another cell
Replies
1
Views
590
bebo021999
B
S
  • Solved
Formula to identify a value within an array
2
Replies
10
Views
723
Fluff
Fluff
D
  • Question
Trying to determine if I'm utilizing VBA Code correctly
Replies
0
Views
505
DonEB
D
D
  • Question
Find longest path of 1s through an array of 0s
Replies
9
Views
1K
dropkickweasel
D

Forum statistics

Threads
1,221,526
Messages
6,160,340
Members
451,637
Latest member
hvp2262

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top