Issues with averaging 3 values with conditions applied

[mcaanda]

I have three values that I need to average, as long as they are w/in a specific range of another but to return a "fail" value is they are not falling w/in the limiting range.

Here's what I have:
A2: 7.46
A3: 9.47
A4: 7.47

If the values of the items listed above are w/in 0.1524 of another, the average can be applied. Such that - the values of A2 and A3 should not, being that they exceed said limit, but A2 and A4 can. Also, if say A3 and A4 "were" w/in the limits it would average those two values and return this value to the cell.

If ALL were w/in the specific range of another, they would all be averaged.

If NONE were w/in the specific range ( A2 and A3 or A3 and A4 or A2 and A4 ) of another specified, a FAIL result would be displayed.

I've tried to come up with if / and / or statements but I think this one is a bit more difficult than I can code so with humble regard I ask for assistance.

Thanks!

[zbor]

Hi... Here is solution for XL 2007, but I need some more time to adopt it for 2003

[zbor]

And here is for 2003:

However, this approach would fail if you have values like this:

1
1.1
3
3.1

Because it will calcualte average of all of them... If that can happend then tell me.

(But it will work for :

1
1.05
1.1
3
3.1

where result is 1.05)

[mcaanda]

I've attached a file ( which I should have done the first time ) to show what what the data type is. This is repetitive, the P,D,D being the data "trio" that I'm looking to find the average for which will create issues with using the whole column for the average. I highlighted the first couple three for ease of display.

This is also in 2007 also.

[zbor]

Please, update your profile then... Soluction can vary with 2003 and 2007.

Also, did you try that solution in example?!

Solution is 3 (there is most data with result =3)

[mcaanda]

ZBor -

Thank you for your assistance, I really so appreciate it. I have updated the profile ( and will finish the rest of it here shortly as well. )

I have looked at the sheet, but am finding it difficult to come up with the averaged values for the depths that are listed in col B.

Here's an image of what I see when I open the file, and I have marked it a bit to visually show what I'm after.

Thanks again...

[zbor]

You have upload nearly 5000 values.
But you want only average for first 3, right?

Then adopt solution for only 3 values as I showed you in post #2.

Does that solution give you what you need (together with comment and solution in post #3 which is for 2003 but comment is for both)

[mcaanda]

You have upload nearly 5000 values.
But you want only average for first 3, right?

No, I need the average of the first three producing a single averaged value, then the 2nd three values averaged to a single value, then the third three and so forth till the end of the file. File lengths are going to vary, as this is only one of a small portion of the data set.

[zbor]

This maybe:

[mcaanda]

Score! This give the desired result - thank you very much.

[zbor]

Actually... there was an error... Please check now attached file again...