Averageifs function not returning correct average

I have a scenario where i must determined the average number between an upper and lower bound (Ylower, yupper)

The upper and lower bounds are calculated separately in the spread sheet and represent the depth of a concrete section. I have a separate array of values which contain all possible section depth.

I need a function that will return the average value of the section depth between these bounds, noting that often one of the bounds is equal to a value 1.696 which is a constant depth for a significant length of the member.

I am not sure why this is not working, but when one of the bounds is the 1.696 value (max height) then the average returned in equal to this number. The function appears to be working for my other scenarios.

I have tried to explain this as clearly as possible but have found it difficult. I have attached the section of the spreadsheet which should be a bit clearer. There is also a pdf explaining the situation.

Any help would be greatly appreciated, because I have hit a roadblock surfing through the internet for solutions.

Cam

Hi Glenn,

I am going to put a nominal array of numbers below to try and make it less confusing because the original spreadsheet is not very nice.

I have 2 x values representing the lower and upper bound.

Bounds (2,7)

I also have a table full of all possible x values in column A and respective heights in column B as follows:

Xlower 2
Xupper 7

A B
x y
1 2
2 2
3 2
4 2.5
5 3
6 4
7 5
8 4
9 3.5

I need my function to find the corresponding y value that relates to the upper and lower bound (2 and 5 respectively). I then need to find the average of the y values that exist between these two numbers.
The average calculation manually would become: Yave=(B2+B3+B4+B5+B6+B7)/6 or (2+2+2.5+3+4+5)/6.

What I have currently done is the following:

-Use vlook up to calculate the corresponding y value for x=2 and x=7, (2 and 5)
-I have then used averageifs of the full range of values in column B between 2 and 5.

I hope this has made it simpler for you. Thanks for your help!

That makes sense! That is what I am after.

Thank you very much for your help.