+ Reply to Thread
Results 1 to 19 of 19

lookup and return a range of data from meteorological data

  1. #1
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    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 -0C and -20C. Ideally, the formula would look for areas of RH (D) greater than 80%, and see if they lie at all within -0C and -20C, 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 -0C and -20C. 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

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: lookup and return a range of data from meteorological data

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Administrative Note:

    Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

    With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

    Thank you for helping us to help you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    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?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: lookup and return a range of data from meteorological data

    You cant. However, if you start the same query over there, we can close this one. If you want to move to the commercial services section, be aware that its not a fast track to getting an answer: members with access to it are limited, so in many ways you are better staying on the free forum. Regardless of where you post, it will be a volunteer who responds, so it will be in their time-frame. Hope this makes sense.

  5. #5
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: lookup and return a range of data from meteorological data

    Just be patient - we are all in different time zones and help out in our free time.

    You still need to upload a sample workbook.

  7. #7
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

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

  8. #8
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    I figured out how to upload - sorry about that.
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: lookup and return a range of data from meteorological data

    Why should your helper have to create sample data when you can easily provide it? You dont need to provide the entire dataset - just that desensitised subset shown.

    Im certainly not going to set about copying a picture into Excel - provide a sample file and then Ill see if I can help.

  10. #10
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    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.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,540

    Re: lookup and return a range of data from meteorological data

    You only had to ask! Ill have a look later.

  12. #12
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    thank you for that!

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,100

    Re: lookup and return a range of data from meteorological data

    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.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  14. #14
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    Quote Originally Posted by JeteMc View Post
    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.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,100

    Re: lookup and return a range of data from meteorological data

    For the file attached to post #13, should the RH & Temp range be from 110 to 208 or should there be two ranges, one from 110 to 113 and another from 160 to 208, or am I missing the point?
    It may be helpful to include an Excel file that has a representative data set and some manually included output that we can attempt to replicate using formulas/code.

  16. #16
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    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.
    Attached Files Attached Files

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,100

    Re: lookup and return a range of data from meteorological data

    I assume that you would like the reporting in feet.
    Looking back at post #13 the order of the columns is shifted to Temp, RH, RH & Temp met and RH met
    The last three are repeated for 87, 90 and 93 percent relative humidity.
    The cloud bottom and top calculations are then placed in cells X3:Z16 on the Data sheet using the same formulas as in post #13.
    On the Output sheet the Thick Cloud Rule cells are populated using: =Data!Y5&" - "&Data!Z5
    I am not sure how you would account for small breaks below a given percentage.
    The formulas account for rows of data that have no values in columns B:D and F so maybe that will be of some help.
    Let us know if you have any questions.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    06-22-2020
    Location
    California, US
    MS-Off Ver
    2016
    Posts
    10

    Re: lookup and return a range of data from meteorological data

    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?

  19. #19
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,100

    Re: lookup and return a range of data from meteorological data

    As seen in AB5:AC13 the following formulas will yield the start and end points of each layer having RH >= 87% and Temp between zero and -20.
    Start point: =INDEX(I$2:I$6698,AGGREGATE(15,6,(ROW(I$2:I$6698)-ROW(I$1))/(O$2:O$6698=1),ROWS(AB$5:AB5)))
    End point: =INDEX(I$2:I$6698,AGGREGATE(15,6,(ROW(I$2:I$6698)-ROW(I$1))/(I$2:I$6698>AB5)/(O$2:O$6698=0),1)-1)
    Let us know if you have any questions.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Lookup a date range by Matching a column data set in a table & fetch respective data
    By chakkrav in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-02-2020, 04:21 AM
  2. Date to number (importing .epw meteorological data)
    By DavidStanek in forum Excel General
    Replies: 7
    Last Post: 11-16-2018, 03:05 PM
  3. [SOLVED] VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)
    By Ebalinska in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-14-2016, 05:55 AM
  4. [SOLVED] Formula to lookup a range of numbers and then return data in next 2 columns
    By justmeok in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-15-2013, 11:54 PM
  5. Changing VBA code to return data range, appending to prior data
    By camcrazy08 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-21-2007, 10:53 AM
  6. return range of data from lookup function
    By Boom1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-18-2006, 12:20 PM
  7. Lookup &amp;amp; Return Range of Data
    By shehasclass in forum Excel General
    Replies: 2
    Last Post: 04-06-2006, 11:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1