Calculate Average of Column B, IF Column A has 2 criteria: >=Col G (AND) <= Col H

Hi there,

I am struggling for some time now with what I believe s an AverageIf formula, and would appreciate any help.

I have 2 columns of data:
Column A = Wind direction
Column B = Wind speed

Columns G and H define the min/ max values that I need to look up in column A, in order to average the corresponding values in column B. Please refer to the attachment, which will help.

My Calculation Problem:
I am trying to average the wind speed between 2 criteria. For example, average Column B IF Column A is >=11.26 [col G] AND <= 33.75 [col H], and am struggling with getting the formula to work in column L.

N.b. I am trying to analyse data as a 16-segmented compass, each of 360/16 = 22.5 degrees.

I have tried the formulae below in cells L5 and L6, but cannot get these to work. Calcs are more likely to work for NNE to NNW, however there is more difficulty for N due to the overlap for >348 BUT <11, so this formula is likely to be different:

'=AVERAGEIF(B3:B27,(($A$3:$A$27,">="&G5)+(COUNTIF($A$3:$A$27,"<="&H5))))
'=AVERAGEIF(B3:B27,(IF(AND($A$3:$A$27">="&G6,$A$3:$A$27"<="&H6))))


For reference, I have managed to do a frequency analysis for each of the 16 directions, which I thought would be quite similar to the problem above (e.g. for E, midpoint is 90, taking min as 78.76 and max as 25).

I hope someone will be able to shed some light on my attempts!

Thanks!!