How to remove number(s) from average formula

[gary153]

I have a formula that averages up to 6 numbers. Sometimes there is less than 6 numbers in the range for me to average.

Often there is an anomoly in the numbers with one number not consistent with the others.

The formula I am currently using is:

=IF(ISERROR(AVERAGEIF(AI4:AI9,"<>0")),"0.00",AVERAGEIF(AI4:AI9,"<>0"))

The numbers to be averaged might be like this

0.05
0.08
-0.02
-0.01
0.02
0.70

= average 0.14

The 0.70 is the anamoly - is there a way to change my formula to exclude any number that is more that .3 different from the lowest number in the range? That would deliver an average of 0.02

Thanks

[FDibbins]

Try taking a look at averageifS() for this - it allows for more than just 1 argument.

Also, remove the "" from around the 0.00...this returns a text string, not a value.

Another way to write your formula...
=IfERROR(AVERAGEIF(AI4:AI9,"<>0"),0)

[gary153]

Thank you for the simplification of my original formula. I had tried averageifs with my original formula but couldn't seem to get it right. I will have to keep playing with it and see what i can do.

[gary153]

Still can't seem to get the syntax right for the averageifs statement to exclude numbers >.3 than min in range. Could anyone please help with how I structure the range and criteria?

Thanks again

[FDibbins]

So >.3 from the next lowest value?

What if there had been 2 0.7's?

[gary153]

No, I really want to exclude any numbers that are more than .3 larger than the smallest number.

So, if the smallest number was 0.00, exclude anything .3 and larger and then average what is left.

[AliGW]

Try this:

=AVERAGE(FILTER(A1:A6,A1:A6<=MIN(A1:A6)+0.3))

where A1 to A6 conitains:

0.05
0.08
-0.02
-0.01
0.02
0.70

[AliGW]

I see that you have never marked previous threads as solved.

If this takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[gary153]

Thank you - this formula seems to work if ther are 6 numbers in the range. Sometimes there are not 6, but less, so part of the range might contain zero cells. Sometimes the range contains a N/A cell. The normal average ignores these but this formula seems to include them in the average.



0.07
0.38
0.34
(with 3 blank cells)

returned 0.02 with the above formula (should have been 0.07). I think it counted the cell with 0.07 and the 3 blank cells and divided by 4 cells. If all 6 cells in range are numbers, then I think this will work, will have to test it but is there a way to ignore blank cells and N/A cells?

[AliGW]

Gary - please provide REALISTIC data from the get go in future.

Attach a workbook that incliudes all eventualities and then I'll look again.

seems to work if ther are 6 numbers in the range

It works for what you asked for!!!

[gary153]

Thanks, I will try and put up an example.

You may not have seen my original post:

I have a formula that averages up to 6 numbers. Sometimes there is less than 6 numbers in the range for me to average.

That's a big part of where I was having the problem.

Thanks so much for your efforts.

[AliGW]

Of course I saw (and read) your original post!!! How could I have offered a suggestion otherwise?

Sometimes there is less than 6 numbers in the range for me to average.

That (to me) meant that the range might be smaller. It did not explicitly state that there could be blanks or #N/A values, nor does your thread title hint at this.

WHEN you have provided a fully realistic data sample, I will look again. I will wait until I have this.

[gary153]

Sorry, I obviously didn't explain it well enough. I thought by posting the formula that I currently use - showing it averaging 6 cells and stating that there are sometimes less than 6 numbers would explain and indicate that the other cells when less than 6 numbers would be blank. I wasn't aware they could be anything else.

I can't find a way to close the thread without marking it as solved - I don't use this forum very often so not very familiar with the tools.

Thanks again for your efforts and apologies for not explaining it well to commence.

[AliGW]

There is NO NEED to close the thread. All you need to do is post a sample workbook with some realistic data and then we can take it from there.

To be clear: sharing a formula that you know does not work doesn't really help.

Please attach a small sample workbook as requested and then I'll have another look. Why is providing a sample workbook a problem?

In the sample workbook, please show us what this looks like:

Sometimes there are not 6, but less, so part of the range might contain zero cells. Sometimes the range contains a N/A cell.

[gary153]

The workbook is not mine - I will need to get permission to share any form of data or extract data into a mockup sheet which I will look at doing when I get finished with some deadline stuff on my desk.

The formula I posted does work - I have been using it for years. What I wanted to do was to modify the formula to allow me to differently analyse the data and rule out any anomolies without having to manually remove cells.

[AliGW]

I just want a sample workbook that includes data including an outlier, blanks and #N/A - I don't need to see the real data. You have already shared data - that's what I want, but in a sample workbook.

I'm really trying to help here ...

[AliGW]

OK - one final guess, then:

=LET(f,FILTER(A1:A6,IFERROR(A1:A6<>"",0)),m,MIN(f)+0.3,n,FILTER(f,f<=m),AVERAGE(n))

[AliGW]

No response and now gone ...

Hopefully you'll see this later. If it works, please mark the thread as solved, etc., as explained earlier. Thanks.