Excel 2007 : AVERAGEIFS - a few conditions put together

**stojko89** · 05-22-2009, 07:17 AM

I checked the forum and there wasnt and example like this so pls help.

what im tryng to do is
get the average of the 6 rows that have something written in it but the conditions are:
if its not a number, dont take it into counting
and if possible dont take the smallest and biggest number like this:

Please Login or Register  to view this content.

Any idias?

TNX

**NBVC** · 05-22-2009, 07:50 AM

Because we have to check for a number in the range.. I am not sure you can use the AVERAGEIFS function...

Instead try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER not just ENTER.. you will see { } brackets appear around the formula

**stojko89** · 05-25-2009, 03:40 PM

So is it posible to make from this:

Please Login or Register  to view this content.

To something like if there are no numbers in the box from B2:G2 then just leave empty space or type 0 ?

I tryed something like this

Please Login or Register  to view this content.

Doesnt seem to work...anyone? help please. couse if i dont have values in the rows yet it shows and error for trying to do 0/0 or something like that.

Ty

**NBVC** · 05-25-2009, 03:54 PM

Try:

Please Login or Register  to view this content.

**DonkeyOte** · 05-25-2009, 04:13 PM

You could perhaps avoid Arrays and use:

=IF(COUNT(B2:G2)<=2,"",(SUM(B2:G2)-MIN(B2:G2)-MAX(B2:G2))/(COUNT(B2:G2)-2))

ie check you have more than 2 numbers (given you are removing smallest & biggest) ... if you do SUM all less Smallest & Largest and divide by count of numbers (less 2 obviously)

In AVERAGEIFS terms:

=AVERAGEIFS(B2:G2,B2:G2,"<"&MAX(B2:G2),B2:G2,">"&MIN(B2:G2))

In fact I think these are all flawed if you have 1+ value @ Min / Max.

**DonkeyOte** · 05-25-2009, 04:19 PM

Another alternative perhaps...

=IF(COUNT(B2:G2)<=2,"",SUMPRODUCT(LARGE(B2:G2,ROW(INDIRECT("2:"&COUNT(B2:G2)-1))))/(COUNT(B2:G2)-2))

(I'll go away now...)

**stojko89** · 05-26-2009, 12:14 AM

Originally Posted by NBVC

Try:

Please Login or Register  to view this content.

This worked just fine... I saw the other options under this one when i tryed that one out but i think im going to use this one.

Thank you for ur help