lookup and return a range of data from meteorological data

Hello! I have been trying to automate a stoplight chart decision matrix for our weather analysis that is provided to assist in the launch of rockets into the upper atmosphere/space. The weather company uses radiosondes attached to weather balloons to fly real high and return atmospheric conditions in the form of large files. See below for refined snippet of this file (it is just a snippet, the real file ingested is over 6-20k rows with many more columns). I'm trying to save time for our meteorological team by automating a few things we are currently hand-jamming. The less time we spend trying to scroll through all this data and understand it, the more time we can spend observing and mitigating the impacts to launch operations. There are two separate issues that I look to solve.

1.) We have a rule here for what we call "thick cloud" rule - in short it helps us prevent against a phenomena called triggered lightning, which could strike the craft at launch. All of the lore aside, What I would like this document to do is return a range of values. This range would ideally be returned in Geopotential height (E), but It would look at RH (D). The criteria for this thick cloud is any chunk of moisture/cloud that is over %80 Relative humidity, and in a layer thicker than 4,500ft - but between or extending beyond -0°C and -20°C. Ideally, the formula would look for areas of RH (D) greater than 80%, and see if they lie at all within -0°C and -20°C, and if they do, return the tops and the bottoms in Geopotential height (E). lets say for simplicity sake there was 90% RH between 100ft and 5,000ft and some of this or all of it lied between -0°C and -20°C. The formula or script would see all of this and return something as simple as "4,900". This is a rather complex issue for me that I have struggled to find a solution to. I was guessing it would have something to do with embedded lookups, or maybe a VBA function but I don't know where to start as my knowledge of complex functions is limited.

2.) The second is what I would think would have a much simpler solution. I would just need this second formula or script to lookup any values above 80% RH (D) and return the top and bottom of that range in Geopotential Height to try to understand how thick clouds cloud be. So If 80% RH started at 3,000ft in Geopotential height, and went all the way to 4,800 feet, where it would then go back below 80% RH, I would want this formula to report in a cell "3000 - 4800". I'm familiar with index and match, but I can't seem to get them to find an array of numbers. Another side issue with this is, sometimes in the atmosphere the RH wavers between 79-80 for a few feet, sometimes only 100 ft thick.. Is there any way to basically have the formula a bit "smarter" in this regard? could we have it just overlook that small gap of when the RH would go below 79 for maybe a set value of lets just say 100ft? In an attempts to clarify, lets say we had 80%RH from 3000 - 4800 Geopotential height again, but this time it dipped below 80% RH to 78RH for 90ft, and then came back up to over 80% at 4890ft all the way to 5,500ft. Would there be a way to just have it omit that small chunk of lower RH and return "3000 - 5500"?

Thank you for your time, and in advance for any help that could be provided towards this.

Data.PNG

Thank you for the welcome!

I went ahead and changed my location. I might actually want to move this post to the commercial services - what would be the best way to do that?

Makes sense. I'll just stay here then. Thanks again!

Does the image not give enough information? The rest of the file is over 6k rows of different meteorological data and proprietary information.

I figured out how to upload - sorry about that.

Truth be told, I just couldn't figure out where the upload window was. I'm new! See above post for attached file - sorry for the hassle.

thank you for that!

Originally Posted by JeteMc

This proposal employs four helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper column (RH > 80%) is populated using: =IF(D2="",I1,D2>=80)
The second helper column (0° > Temp > 20°) is populated using: =IF(C2="",J1,AND(C2<=0,C2>=-20))
The third helper column (RH & Temp Conditions Met) is populated using: =IF(OR(I2=FALSE,J2=FALSE),0,SUM(K1,1))
The fourth helper column (RH Condition Met) is populated using: =IF(I2=FALSE,0,SUM(L1,1))
The cloud top formulas are similar to: =AGGREGATE(14,6,E2:E20/(K2:K20=MAX(K2:K20)),1)
The cloud bottom formulas are similar to: =INDEX(E2:E20,AGGREGATE(14,6,(ROW(E2:E20)-ROW(E1))/(K2:K20=MAX(K2:K20)),1)-MAX(K2:K20)+1)
Note that the values in the RH% column have been changed to test the formulas.

JeteMc,

Thank you for the reply! I learned a new function today, I didn't know aggregate was a thing! This definitely gives me a strong foundation to build from.
One limitation that I've noticed is that it doesn't check for multiple layers of RH above 80%. When I put the data into the workbook, the data would have several blocks of layers above 80% RH, and I'd need it to see all of them.

For just the RH%, I'm not sure how you would get it to do this but it would be any area over 80%. The bigger more interesting issue is having the formula omit small breaks below 80%, and I think you'll see why.. maybe having most of the data would help. See attached.
On the output sheet at the bottom of that page is where I would want to put the different blocks of moisture in kind of a "cloud base - cloud top" format.. i.e. "1409 - 3807"

Not sure if it really matters here but I have two different height columns on the "data" sheet, geopotential height is in meters, and the one labeled "feet".. is... in feet.

Wow! Great stuff! - Thank you again for your help.

I'm seeing RH above 87 at 1841 - 2221 but this isn't listed on the formulas. I was wondering if there was any way to get separate data sets for RH..
for instance, If there was two layers in the entire column that were 2000-5000 and 7000-9000.. is there any way we could show both - or all of the areas that meet that criteria?