Filling In Data Based On Half-Hour Times

[windme]

Hello Everyone,
Using Excel 2013

I have a spreadsheet that has weather data from the internet. The data table on the left has numerous times in column A, which vary from hour to hour. The table on the right shows half hour increments in columns K & L with selected data from the left table. I have made up formulas to extract the data from left table, but I would like to somehow add to the formulas to have it search columns K & L (time range) and fill in the data for that half hour in columns M to P. Something like =IF(A:A>=00:01<=00:30, then attach it to each formula above. Any help or suggestions would be appreciated, thanks.
Attached is the sample spreadsheet.

Mike

[Bob Phillips]

Here is an array formula that does it

Please Login or Register  to view this content.

[windme]

Hi Bob,
thanks for the response. I tried to add the if statement for the max formula and I receive the #VALUE! error. I like to have your formula find the location of the time and do the four formulas that i have under the right table. How can I correct that? thanks

Please Login or Register  to view this content.

[windme]

When the time range is found in K and L, i need to have the 4 formulas adjusted to that range. In the example that shows the 4 formulas

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

i need to have the range in the formulas to match columns K & L. If the times in column A show that 00:01 to 00:30 range is A2:A6, i need those 4 formulas have the same row adjusted, in this $8 changes to $6.

[canapone]

Hi,

another approach in M2 to be confirmed with control+shift+enter

Please Login or Register  to view this content.

in N2:

Please Login or Register  to view this content.

In O2 (as MAXIFS will available from Excel 365-2016)

Please Login or Register  to view this content.

In P2:

Please Login or Register  to view this content.

Please refer to the attachment.

Regards


Edit: I'm translating formulas from Italian Excel version: please doble-check if I've forgotten some ";" as delimiter (sorry)

[windme]

Canapone,
You hit the N2, G2, and P2 right on the head!! awesome!

The M2 formula that counts the most speed direction text works for M2 (SSE), but M3 and M4 also give SSE, should be SE and NNE. If that could be corrected with be helful.
Thank you.

[windme]

Canapone,
I forgot the control, shift and enter...it's working!

Thanks again for your help

[canapone]

Hi again

first off, forgive my poor English

Formula in M2 must riconfirmed with control+shift+enter and if copied down, should return "SE" and "NNE" (please see the attachment)

The problem I can see in M2 is there are two "most speed" directions.

In these cases MODE.MULT can be of some help: after copied the formula in R2, you have to select R2:S2, press F2 (function F2) and - again - riconfirm with control+shift+enter.

In this way you can check if there are two different "most speed" directions using:

Please Login or Register  to view this content.

Again, this one is a formula for "two cells" (see the yellow formula in the attachment)

Hope you'll get better solutions.

[windme]

Hi Canapone,
Sometimes there will be two or more criteria that are equal, i usually accept with excel gives me; but just wondering if the tie could be broken by adding up the adjacent speed and have tie broken. In the example of the SE and SSE, the 3 SSE speed add up to 20 and the 3 SE add up to 23, so SE would be placed in M2. If there's no way to do that it would be no problem at all.

Thanks

[canapone]

Hi again

I have only a not very elegant solution to share, using a couple of sumifs near the yellow formulas


In R2

=SUMIFS(H:H;A:A;">="&K2,A:A,"<="&L2,G:G,R2)

in S2

=SUMIFS(H:H;A:A;">="&K2,A:A,"<="&L2,G:G,S2)


and then in C2 to elaborate together MODE.MULT and SUMIFS


=IF(T2<U2,S2,R2)

I strongly suspect there could be better solutions

[windme]

thanks for those tiebreakers Canapone