I have a spreadsheet that I need some indexing help with. See Picture: Columns A-D are pasted in, column E is just logic, so I need a way to make column F calculate the difference between the last and first non-zero number of each of those non-zero chunks. If the difference is >= 2, then I need it to spit of the corresponding bottom and top number of that chunk from column A.


[Edited on March 29, 2015 at 11:28 AM. Reason : .]

3/29/2015 11:28:24 AM

i think i understand what you're asking for, but can you post a screenshot with an example of what you want in the F column?

[Edited on March 29, 2015 at 3:40 PM. Reason : you want F68 to be 314.2974, F77 to be 316.5668 and the rest of column F to be empty?]

3/29/2015 3:25:39 PM

Column F is the difference between the bottom and top value from E of the non-zero groups. I would like for column G to return the corresponding value from A for the top and bottom of the chunk provided the value from column F is >= 2. Right now, I have it column G giving one of the values, but I don't know how to make it reference the first row of that non-zero group.


To clarify, I would be elated to have the highlighted values returned in columns G & H if the difference between between the top and bottom non-zero values from column B (or E) is >=2.

[Edited on March 29, 2015 at 4:38 PM. Reason : .]

3/29/2015 4:28:20 PM

it's a little dirty, but this worked

https://drive.google.com/file/d/0BxfUPsatR6oXWHNibkZpbHdjS0U/view?usp=sharing

3/29/2015 6:08:32 PM

Thank you very much! I should be able to jimmy with that and get it integrated into my worksheet. What I am doing is using the calculated potential temperatures from upper air balloon launches to derive the atmospheric boundary layer (ABL). Using something called the Heffter Technique, it is the lowest inversion layer (temp increasing with height) where the potential temperature lapse rate (how it changes with height) is above a certain critical threshold (the column of 0.005, 0.004, etc) and the temps at the top and bottom of the layer are >= 2 Kelvin. If a layer meets that criteria, then the ABL is the height that is 2 Kelvin above the bottom height/temp of the inversion layer.

3/29/2015 6:57:57 PM