Excel 2007 : Getting the average value when source data includes blanks?

Hi,

I was wondering how I should modify my code so that it will reliably calculate average values without throwing me DIV/0 errors for data that is all blanks or returning a blank to me because my formula isn't written very well

=AVERAGEIFS(Sheet1!$H:$H,Sheet1!$AS:$AS,B$1,Sheet1!$D:$D,$A2),"")
This formula will give me the DIV/0 error

=IF(COUNTIFS(Sheet1!$AS:$AS,B$1,Sheet1!$D:$D,$A2,Sheet1!$H:$H,NOT("")),AVERAGEIFS(Sheet1!$H:$H,Sheet1!$AS:$AS,B$1,Sheet1!$D:$D,$A2),"")
This formula gives me only blanks

The range of values in Sheet1!H:H is from 0-10 and blanks. The formula needs to reliably exclude blanks completely.

Also, can I express my formula in any simpler shape or form?

Thanks

=SUMPRODUCT(--(Sheet1!$H:$H>0)*(Sheet1!$H:$H<>"")*(Sheet1!$H:$H))/SUMPRODUCT(--(Sheet1!$H:$H>0)*(Sheet1!$H:$H<>"") This will give the average of all cells in H:H that are not an empty string and are greater than zero. The first two arrays return trues and falses which are converted to 1 or 0. The resulting array is then multiplied by values in the H column so that only values meeting the criteria are returned. This result is divided by the same array in the second sumproduct formula but the results are not multiplied by the values, so you are getting the sum of all divided by the count. You can then tweak for the AS portion.

Sumproduct is backwards compatible, but calculates as an array formula so it can bog things down when you use whole columns. I saw you were using AverageIfs which is an Excel 2007+ formula, so the following should also work:

=IFERROR(AVERAGEIFS(Sheet1!$H:$H,Sheet1!$D:$D,$A2,Sheet1!$AS:$AS,B$1,Sheet1!$H:$H,"<>"),0)

It will average non-blank cells cells in column H for which D and AS meet criteria. If there is nothing that meets critieria, the output will be a 0.

Private message from dip11:

Originally Posted by dip11

One question I had in regards to your code was what <> actually does? I tried removing it and the results were the same.

For reference:
=IFERROR(AVERAGEIFS(Sheet1!$H:$H,Sheet1!$D:$D,$A2,Sheet1!$AS:$AS,B$1,Sheet1!$H:$H,"<>"),0)

Thanks

The <> tells Excel "Does not equal", so "<>" means, "Does not equal nothing", or in other words, it tells excel to ignore cells that are blank.

Now for the ugly part. From the forum rules (link in my sig): Don't Private Message or email questions to moderators or other members. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.

If the results are identical for you with or without that part, try leaving out the last two arguments of the averageifs formula. That would allow excel to calculate it a bit a faster:
=IFERROR(AVERAGEIFS(Sheet1!$H:$H,Sheet1!$D:$D,$A2,Sheet1!$AS:$AS,B$1),0)

If that does not yield the desired results, then leave the last two arguments in. With or without the <> inside the quotes, the formula will calculate at pretty much the same rate.